A query is an ordered list of statements. Each statement generates a single named set of records.
<Query> ::= <Statements> <Statements> ::= <Statement> [; <Statements>]
<Statement> ::= (DEFINE | RETURN) <Key> AS <SELECT> [<FROM>] [<WHERE>] [<GROUP BY>] [<HAVING>] [<ORDER BY>] [<PAGE>]
Item | Description and Syntax | |
---|---|---|
1 | A name for the resulting record set (DEFINE | RETURN <recordSetName> AS) |
In most cases, you will use RETURN. Use DEFINE when you are generating a set of records to use in a later statement. |
2 | The attributes (or derived attributes) that the set of records should contain (SELECT expressions) |
<Select> ::= SELECT <Assigns> <Assigns> ::= <Assign> [, <Assigns>] <Assign> ::= <Expr> AS <Key> Expressions can perform a variety of functions. You can use commas to separate multiple expressions within a statement. |
3 | The input record set (FROM) |
<From> ::= FROM <Key> ::= FROM NavStateRecords ::= FROM AllBaseRecords The input records provide the values used in expressions and
WHERE filters. The input record set is either:
|
4 | An optional filter to apply to the input (WHERE) |
<Where> ::= WHERE <Filter> As in SQL, the WHERE clause filters the inbound records before any operations are performed on them. |
5 | A way to group the output records (GROUP BY) | Used to map the input records to the output
records.
<GroupBy> ::= GROUP ::= GROUP BY <Groupings> <Groupings> ::= <Grouping> [, <Groupings>] <Grouping> ::= <Key> ::= <Key>:<int>
The colon operator allows you to group by a hierarchical attribute at a specified level of the hierarchy. |
6 | An optional filter to apply to output records (HAVING) |
<Having> ::= HAVING <Filter> As in SQL, the HAVING clause filters the results of the operations on the input records. |
7 | An optional way to determine the sort order of the output records (ORDER BY) |
<OrderBy> ::= ORDER BY <OrderList> <OrderList>::= <Order> [, <OrderList>] <Order> ::= <Key> [ASC | DESC] The ORDER BY clause includes the comma-separated list of attribute(s) to use for the sorting, and whether to sort from smallest to largest (ASC) or largest to smallest (DESC). The default order is to sort in ascending order. |
8 | An optional way to page through the output records, in other words, to return only a subset of the output (PAGE(i,n)) |
<Page> ::= PAGE(<int>,<int>) In the syntax, the first value is the number of the record to start at, and the second value is the number of records to return. |