We are using cookies to give you the best experience on our site. Cookies are files stored in your browser and are used by most websites to help personalise your web experience.
COEL formula represents a mathematical function which computes one output value for input values. One COEL expression determines how one output value is computed while it is the task of the engine to loop through all inputs and store the outputs.
Compare how COEL expressions are different from spreadsheet formulas. For example, the following formula defines a value in the cell
C1 as a sum of values in
= A1 + B1
Note that the value of only one cell is defined and will be computed. Each cell has two coordinates.
In COEL, we define columns rather than cells and an analogous formula computing column
C from columns
B could be written as follows:
= A + B
Note that now the arguments have only one coordinate: column name. This means that the system will evaluate this formula for all rows (inputs) by producing all outputs rather than a single value. This is why we call it column formula and not cell formula.
Calculated columns return a primitive value which is computed from input values read from this same table. Assume that table
LineItems has two columns
quantity. We can compute a new column
amount as follows:
[amount] = [price] * [quantity]
Each value of the
amount column is equal to the product of values in the other two columns from same row of the table.
Link columns are thought of as a substitute for joins.
A COEL expression can return an element of non-primitive table. Note however that it is not possible to compute a non-primitive output value using arithmetic operations. To return a non-primitive value, it is necessary to define a tuple from the output set. In other words, if an expression returns a tuple then this means that it returns a value representing the corresponding (matching) element from the output set.
Assume that we want to define a new column
order to the table
LineItems which returns a purchase order this item belongs to from the table
Orders. This can be done as follows:
[order] = TUPLE( supp=[supplierId], no=[orderNo] )
no are column of the output table
orderNo are columns of the input table
Importantly, once the link column
order has been defined, it can be used as a normal column in other expressions which returns an order for each line item.
Accumulation columns are thought of as a substitute for group-by operation.
An accumulation column is able to aggregate multiple values during evaluation of its formula. These multiple values are produced while looping through some other table which is referred to as a fact table.
For example, we could add a new column
total to the table
Orders by summing up the prices of all line items belong to this order:
[total] = ACCU( [LineItems], [order], [amount], SUM )
The accumulation function takes four parameters:
LineItemsis a fact table
orderis grouping column which specifies an order this fact record belongs to. It is a link column which can be an arbitrary COEL expression.
amountis a measure column the output values of which are being accumulated. It is an existing column or an arbitrary calculated column.
SUMis an aggregation function which specifies how the multiple values returned by the measure column have to be accumulated.