Basic syntax of LQL queries and statements

A query is an ordered list of statements. Each statement generates a single named set of records. 

<Query>        ::=  <Statements>
<Statements>   ::=  <Statement> [; <Statements>]
The basic syntax for a statement is: 
<Statement>    ::=  (DEFINE | RETURN) <Key> AS <SELECT>
                       [<FROM>]
                       [<WHERE>]
                       [<GROUP BY>]
                       [<HAVING>]
                       [<ORDER BY>]
                       [<PAGE>]
A statement specifies:
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:
  • The result of a previous statement
  • The records for the current navigation state (NavStateRecords). This is the default value. If you do not provide a FROM clause, then this is the value used.
  • The complete set of records (AllBaseRecords)
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>
  • If you provide a GROUP BY list of attributes, then all records containing identical values for those attributes are grouped (or bucketed) together.
  • If you only specify GROUP, all input records are grouped into a single output record.
  • If you do not specify either GROUP or GROUP BY, then no grouping occurs. The number of output records is the same as the number of input records.

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.