Query Language Syntax

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 Iotellect 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 | FULL OUTER} JOIN table ON expression

table

{ (selectStatement) [AS tableAlias] | contextReference [AS tableAlias]}

contextReference

See context references.

contextMask { :contextEntityReference [: ...] }

contextEntityReference

{ contextVariableName | contextVariableGroupName.* | contextFunctionName ( contextFunctionParameters ) }

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

[+ | -] { literal [{ + | - | * | / | || } literal]

| ( condition )

| function ( [parameter] [,...] )

| selectStatement giving one value

| {ANY|ALL} (selectStatement giving single column)

| INTERVAL literal {YEAR | MONTH | DAY | HOUR | MINUTE}

}

fieldReference

See field references.

{ { contextVariableName | contextFunctionName} $ dataTableFieldName | writebackFieldName }

writebackFieldName

"CONTEXT_ID" | "PARENT_ID" | "RECORD_INDEX"

Literals

Iotellect Query Language defines several types of literals:

  • Null Literal: NULL
  • Boolean Literals: TRUE of FALSE
  • Decimal Literals (0, 1, 123, -1234567890, ...)
  • Hexadecimal Literals (X'0A', X'FFFF', ...)
  • Binary Literals (B'01', B'00110011')
  • Floating Point Literals (3.1, -44.5, 1.3E12, ...)
  • String Literals ('This is a String', 'test', ...)

Was this page helpful?