Syntax:
SELECT …
ON D AS A WHERE …
FROM T BY T.F.....
Parameter:
|
D |
Dimension |
|
A |
Alias of dimension D and AS operator after it can be absent |
|
WHERE… |
Filtering condition on dimension D |
|
BY T.F |
Grouping field |
Note: See the relationships between tables in the previous section.
|
SELECT ReturnedPmt.sum(Amount) Pamount,Orders.sum(Amount) Oamount ON year WHERE year>2011 FROM Orders BY ShipDate#year UNION ReturnedPmt BY Date#year |
Perform filtering on year dimension and calculate total payment and orders amount for records whose payment dates and ship dates are after the year 2011 |
|
|
SELECT ReturnedPmt.sum(Amount) Pamount,Orders.sum(Amount) Oamount ON EMPLOYEE WHERE ([ 1,5,9]).contain(EMPLOYEE.EmpID ) FROM Orders BY EmployeeID UNION ReturnedPmt BY SellerID |
Align ReturnedPmt table and Orders table according to the dimension EMPLOYEE; the dimension filtering condition is that value of dimension field EmpID is a member of sequence [ 1,5,9] |
|
Tips:
1) Write filtering condition in ON clause and the condition on dimension applies directly to each field in BY clause aligned to the field in ON clause.
2) Except for regular single-vale-based judgments (greater than, less than and equal to), you can use an esProc function in the filtering condition on dimension specified in WHERE clause.
3) If fields in multiple tables point to same dimension in the table association, the filtering condition written after the dimension will apply to every involved table.