|
This article explains nothing. It merely shows what SQL constructs and functions may be used within <%AG %>. Actually training the reader in SQL is very much beyond the scope of this manual. If you're not fully conversant in SQL, we highly recommend the reference texts listed in the Queries article. |
Every query in AggreGate Query Language has the following syntax:
selectStatement
SELECT [ALL | DISTINCT]
{ selectExpression | tableAlias.fieldReference | tableAlias.* | * } [, ...]
FROM tableList
[WHERE expression]
[GROUP BY expression [, ...]]
[HAVING expression]
[{ UNION [ALL] | MINUS | INTERSECT } selectStatement]
[ORDER BY orderExpression) [, ...]]
[LIMIT <limit> [OFFSET <offset>]]
|
The LIMIT clause may be used only if all Context References listed in FROM clause have aliases. (I.e. "SELECT ... FROM context_reference AS alias LIMIT n, m") |
tableList
table [joinedTables] [, ...]
joinedTables
joinedTable [joinedTable] [...]
joinedTable
{CROSS | INNER | LEFT OUTER | RIGHT OUTER} JOIN table ON expression
table
{ (selectStatement) [AS tableAlias] | contextReference [AS tableAlias]}
contextMask { :contextEntityReference [: ...] }
contextEntityReference
{ contextVariableName | contextVariableGroupName.* | contextFunctionName ( contextFunctionParameters ) }
{ null | 'value' | "value"} [, ...]
orderExpression
{ [tableAlias.]fieldReference | selectExpression } [ASC | DESC]
selectExpression
{ expression | COUNT(*) | { COUNT | MIN | MAX | SUM | AVG } ([ALL | DISTINCT] expression) } [[AS] label]
expression
[NOT] condition [{ OR | AND } condition]
condition
{
value [|| value]
| value { = | < | <= | > | >= | <> | != } value
| value IS [NOT] NULL
| EXISTS(selectStatement)
| value BETWEEN value AND value
| value [NOT] IN ( {value [, ...] | selectStatement } )
| value [NOT] LIKE value [ESCAPE] value
}
value
[+ | -] { term [{ + | - | * | / | || } term]
| ( condition )
| function ( [parameter] [,...] )
| selectStatement giving one value
| {ANY|ALL} (selectStatement giving single column)
term
{ 'string' | number | floatingpoint | [tableAlias.]fieldReference | TRUE | FALSE | NULL }
{ { contextVariableName | contextFunctionName} $ dataTableFieldName | writebackFieldName }
writebackFieldName
"CONTEXT_ID" | "PARENT_ID" | "RECORD_INDEX"