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]

Iotellect SQL queries follow the standard SQL rules for unquoted identifiers. Unquoted identifiers, including aliases specified with AS, are case-insensitive and are returned in lowercase in query results. For example, SELECT x AS firstName produces a field named firstname, not firstName.

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?