Grammar part | Syntax |
AdditiveExpression | <MultiplicativeExpression> {(+ | - | ||) <MultiplicativeExpression>} […n] |
AggrExpression | COUNT (([ALL | DISTINCT] <ValueExpression> | *)) | (AVG | MAX | MIN | SUM) ([ALL | DISTINCT] <ValueExpression>) |
AndExpression | {<UnaryLogicalExpression>} [AND...n] |
CardinalityType | 1 | ? | * | + |
CaseExpression | CASE [<ValueExpression>] {<WhenClause>} […n] [ELSE <ValueExpression>] END |
CastExpression | CAST((<ValueExpression> | NULL) AS <ScalarDataType>) |
ColumnAlias | IDENTIFIER |
CondExpr | {<AndExpression>} [OR…n] |
ConditionalPrimary | (<CondExpr>) | <SimpleCondition> |
DataType | <ScalarDataType> |
ExplicitInnerOuterType | LEFT [OUTER] | INNER |
ExplicitJoinType | MERGE | NL | DEPENDENT |
ExpressionList | {<ValueExpression>} [,...n] |
FilterClause | FILTERS(IDENTIFIER DataType 'ValueExpression' [,…n]) Use FILTERS only from a report designer. |
FromClause | {FROM <FromTableReference>} [,…n] |
FromTableName | IDENTIFIER [(<TableParameters>)] [[AS] IDENTIFIER] If the identifier is not enclosed in quotes, it is interpreted as a table. If the identifier is enclosed in quotes, it is interpreted as an absolute or relative path in the volume. |
FromTableReference | <JoinExpression> | (<JoinExpression>) | <FromTableName> |
FunctionCallOrColumnRef | IDENTIFIER ( ([<ExpressionList>]) | [. IDENTIFIER] ) |
GroupByClause | GROUP BY {<ValueExpression>} [,…n] ValueExpression can be an expression as long as the expression also appears as a SELECT item. |
HavingClause | HAVING <CondExpr> |
JoinCondition | ON <CondExpr> [ {CARDINALITY('<CardinalityType> - <CardinalityType>')} ] |
JoinElement | (<JoinExpression>) | <FromTableName> |
JoinExpression | <JoinElement> {<JoinSpec><JoinElement> [<JoinCondition>]} […n] |
JoinSpec | [ [ [LEFT | RIGHT] OPTIONAL ] <ExplicitInnerOuterType> ] [ <ExplicitJoinType> ] JOIN |
Length | INTEGER_LITERAL |
MultiplicativeExpression | <UnaryExpression> {(* | /) UnaryExpression} […n] |
NamedParameter | : IDENTIFIER |
OrderByClause | ORDER BY {<ValueExpression> (ASC | DESC)? } [,…n] ValueExpression is not limited to SELECT items or aliases. If ValueExpression is not a SELECT item or an alias, it must be a grouping column if a GroupByClause is present. Use ORDER BY only when creating a query in Information Object Query Builder. Do not use ORDER BY when you create an information object in the IO Design perspective. |
ParameterDeclaration | IDENTIFIER [<AS>] <Data Type> |
ParamPlaceholder | <NamedParameter> |
Pragma | PRAGMA IDENTIFIER := CHAR_LITERAL |
Precision | INTEGER_LITERAL |
PrimaryExpression | <FunctionCallOrColumnRef> | <ParamPlaceholder> | <UnsignedLiteral> | <AggrExpression> | (<ValueExpression>) | <CastExpression> |
QueryParameterDeclaration | WITH ({<ParameterDeclaration>} [,…n]) All parameters are required. |
RelationalOperator | =|<>|<|<=|>|>= |
ScalarDataType | VARCHAR [(<Length>)] | DECIMAL [(<Precision>, <Scale>)] | INTEGER | DOUBLE [<Precision>] | TIMESTAMP |
Scale | INTEGER_LITERAL |
SelectItem | <ValueExpression> [[AS] <ColumnAlias>] |
SelectList | {<SelectItem>} [,…n] |
SelectStatement | (<SelectWithoutOrder> [<OrderByClause>]) | <SelectWithoutFrom> |
SelectWithoutFrom | SELECT <ValueSelectList> |
SelectWithoutOrder | ( ( SELECT [ALL | DISTINCT] <SelectList> <FromClause> [<WhereClause>] [<GroupByClause>] [<HavingClause>] [<SetClause>] ) | (<SelectWithoutOrder>) ) [<SetClause>] |
SetClause | UNION ALL (<SelectWithoutOrder> | <SelectWithoutFrom> ) |
SignedLiteral | CHAR_LITERAL |[+ | -]INTEGER_LITERAL |[+ | -]DOUBLE_LITERAL |[+ | -]DECIMAL_LITERAL |TIMESTAMP TIMESTAMP_STRING |
SimpleCondition | EXISTS <SubQuery> | <SubQuery> <RelationalOperator> <ValueExpression> | <ValueExpression> (<RelationalOperator> ( ( [ ANY | ALL ] <SubQuery> ) | <ValueExpression> ) | IS [NOT] NULL | [NOT] ( BETWEEN <ValueExpression> AND <ValueExpression> | LIKE <ValueExpression> [ESCAPE <ValueExpression>] | IN <SubQuery> | IN (ExpressionList) ) ) The escape character must evaluate to a single character other than a single quote, a percent sign, or an underscore. |
SubQuery | (<SelectWithoutOrder> [ OPTION (SINGLE EXEC) ]) |
TableParameter | (<SignedLiteral> | NULL | <ParameterReference> | <ValueExpression>) |
TableParameters | <TableParameter> [,…n] |
UnaryExpression | [+ | -] <PrimaryExpression> |
UnaryLogicalExpression | [NOT] <ConditionalPrimary> |
UnsignedLiteral | CHAR_LITERAL |INTEGER_LITERAL |DOUBLE_LITERAL |DECIMAL_LITERAL |TIMESTAMP TIMESTAMP_STRING |
ValueExpression | <AdditiveExpression> | <CaseExpression> |
ValueSelectItem | <ValueExpression> [[AS] <ColumnAlias>] |
ValueSelectList | {<ValueSelectItem>} [,…n] |
WhenClause | WHEN <ValueExpression> THEN <ValueExpression> |
WhereClause | WHERE <CondExpr> |