RAQL Query Syntax Reference
This topic contains syntax definitions and corresponding syntax diagrams for the Real-Time Analytics Query Language (RAQL) in Presto and the Event Query Language (EQL) for the webMethods Event Server. Some keywords or specific syntax constructions are relevant only in RAQL while others are relevant only in EQL.
See the following links to find the start of definitions for the valid clauses in RAQL:
For RAQL | Definition Begins From |
Entire query | |
Select clause | |
Over partitions | |
From Clause | |
Join and other set operators | |
Where clause | |
Group by clause | |
Having clause | |
Order by clause | |
Limit clause | |
Action (EQL only)
Applies only to EQL queries in webMethods Business Events.
Alias
Definition:
Alias
::= 'AS'? ObjectName
Used In:
MatchingClause
SelectItem
SourceReferenceOrSubqueryArray Element Reference
Definition:
ArrayElementReference
::= '[' S_NUMBER ']'
Used In:
SqlPrimaryExpressionArray Value Constructor
Definition:
ArrayValueConstructor
::= 'ARRAY' '[' SqlExpressionList ']'
Used In:
SqlPrimaryExpressionBetween Condition
Definition:
SqlBetweenClause
::= 'NOT'? 'BETWEEN' SqlSimpleExpression 'AND'
SqlSimpleExpression
Used In:
SqlRelationalExpressionCase Call, Boolean
Definition:
BooleanCaseCall
::= ( 'WHEN' SqlExpression
'THEN' SqlSimpleExpression )+
( 'ELSE' SqlSimpleExpression )? 'END'
Used In:
FunctionCallCase Call, Simple
Definition:
SimpleCaseCall
::= SqlSimpleExpression
( 'WHEN' SqlSimpleExpression
'THEN' SqlSimpleExpression )+
( 'ELSE' SqlSimpleExpression )? 'END'
Used In:
FunctionCallCharacter, Literal
Definition:
S_CHAR_LITERAL
::= "'" ( "'" "'" | [^'] )* "'"
Used In:
Constant
MatchingClause
MeasureItem
WindowClauseEQLConstant
Definition:
Constant
::= 'NULL'
| ( '+' | '-' )? S_NUMBER
| S_CHAR_LITERAL
| 'TRUE'
| 'FALSE'
Used In:
ConstantList
SqlPrimaryExpression
WindowFunctionCallConstant, List
Definition:
ConstantList
::= Constant ( ',' Constant )*
Used In:
Data Type, Basic Declaration (EQL only)
Applies only to EQL queries in webMethods Business Events.
Data Type, Castable
RAQL has minimal, passive support for the following datatypes.
![*](bullet.gif)
Blob
![*](bullet.gif)
Clob
![*](bullet.gif)
Ref
![*](bullet.gif)
Struct
Dataset columns may contain data of these types, but RAQL provides no access to read or manipulate this data.
Definition:
CastableDataTypeDeclaration
::= ( 'BIG_DECIMAL'
| 'BYTE'
| 'INTEGER'
| 'FLOAT'
| 'LONG'
| 'SHORT'
| 'DOUBLE'
| 'CHARACTER'
| 'STRING'
| 'DATE'
| 'TIMESTAMP'
| 'TIME'
| 'BOOLEAN'
| 'BLOB'
| 'CLOB'
| 'REF'
| 'STRUCT'
| 'UNKNOWN' ) 'ARRAY'?
Used In:
FunctionCallDefine Clause (EQL only)
Applies only to EQL queries in webMethods Business Events.
Define Item (EQL only)
Applies only to EQL queries in webMethods Business Events.
DIGIT
Definition:
DIGIT
::= [0-9]
Used In:
INTEGEREOF: End of File
This character is not actually specified in a query by users as the EOF character is illegal in XML. Instead, this character is automatically added by the parser for validation purposes.
Definition:
EOF
::= $
Used In:
SelectStatementField Definition (EQL Only)
Applies only to EQL queries in webMethods Business Events.
Field Definition List (EQL Only)
Applies only to EQL queries in webMethods Business Events.
FLOAT
Definition:
FLOAT
::= INTEGER ( '.' INTEGER )?
| '.' INTEGER
Used In:
S_NUMBERFrame Boundary (RAQL Only)
Definition:
FrameBound
::= S_NUMBER
Used In:
WindowFrameBound
WindowFrameStartFrom Clause
Definition:
FromClause
::= 'FROM' FromItem ( ',' FromItem )*
Used In:
SelectWithoutOrderFrom Item
Definition:
FromItem
::= SourceReferenceOrSubquery JoinedTable*
Used In:
FromClauseFunction Call
This section diagrams only those plain functions that are built into RAQL which RAQL supports from the SQL standard.
For additional diagrams on other built-in and user defined functions in
RAQL, see
Function Call, Aggregate,
Function Call, Window (RAQL Only) and
Function Call, Generic. For a complete list and reference information of all plain and analytic functions in
RAQL, see
Built-In
RAQL
Functions.
Definition:
FunctionCall
::= 'ABS' '(' SqlExpression ')'
| 'CASE' ( SimpleCaseCall | BooleanCaseCall )
| 'CAST' '(' SqlExpression 'AS' CastableDataTypeDeclaration ')'
| ( 'CEIL' | 'CEILING' ) '(' SqlExpression ')'
| ( 'CHAR_LENGTH' | 'CHARACTER_LENGTH' ) '(' SqlExpression ')'
| 'EXP' '(' SqlExpression ')'
| 'FLOOR' '(' SqlExpression ')'
| 'LN' '(' SqlExpression ')'
| 'LOWER' '(' SqlExpression ')'
| 'MOD' '(' SqlExpression ',' SqlExpression ')'
| 'POWER' '(' SqlExpression ',' SqlExpression ')'
| 'ROUND' '(' SqlExpression ( ',' SqlExpression )? ')'
| 'SQRT' '(' SqlExpression ')'
| 'SUBSTRING' '(' SqlExpression 'FROM' SqlExpression ( 'FOR' SqlExpression )? ')'
| 'TRIM' '(' ( ( 'LEADING' | 'TRAILING' | 'BOTH' )? SqlExpression? 'FROM' )? SqlExpression ')'
| ( 'TRUNC' | 'TRUNCATE' ) '(' SqlExpression ( ',' SqlExpression )? ')'
| 'UPPER' '(' SqlExpression ')'
Used In:
SqlPrimaryExpressionFunction Call, Aggregate
This section diagrams only those aggregate analytic functions that are built into RAQL which RAQL supports from the SQL standard.
For additional diagrams on other built-in and user defined functions in
RAQL, see
Function Call,
Function Call, Window (RAQL Only) and
Function Call, Generic. For a complete list and reference information of all plain and analytic functions in
RAQL, see
Built-In
RAQL
Functions.
Definition:
AggregateFunctionCall
::= ( 'AVG'
| 'MAX'
| 'MIN'
| 'STDDEV_POP'
| ( 'STDDEV' | 'STDDEV_SAMP' )
| 'SUM'
| 'VAR_POP'
| ( 'VARIANCE' | 'VAR_SAMP' )
)
'(' ( ( 'ALL' | 'DISTINCT' ) SourceField | SqlExpression ) ')'
| 'COUNT' '(' ( '*' | ( 'ALL' | 'DISTINCT' ) SourceField | SqlExpression ) ')'
| ( 'CORR'
| 'COVAR_POP'
| ( 'COVAR_SAMP' | 'COVAR' )
| 'REGR_INTERCEPT'
| 'REGR_R2'
| 'REGR_SLOPE' ) '(' ( SqlExpression | SourceField ) ','
( SqlExpression | SourceField ) ')'
Used In:
SqlPrimaryExpressionFunction Call, Window (RAQL Only)
This section diagrams only those window analytic functions that are built into RAQL which RAQL supports from the SQL standard.
For additional diagrams on other built-in and user defined functions in
RAQL, see
Function Call,
Function Call, Aggregate and
Function Call, Generic. For a complete list and reference information of all plain and analytic functions in
RAQL, see
Built-In
RAQL
Functions.
Definition:
WindowFunctionCall
::= ( 'RANK'
| 'DENSE_RANK'
| 'PERCENT_RANK'
| 'CUME_DIST'
| 'ROW_NUMBER' ) '(' ')' WindowClauseRAQL
| 'NTILE' '(' S_NUMBER ')' WindowClauseRAQL
| ( 'LEAD' | 'LAG' ) '('
( SqlExpression | SourceField ) ( ',' S_NUMBER ( ',' Constant )? )? ')' WindowClauseRAQL
| ( 'FIRST_VALUE'
| 'LAST_VALUE' ) '(' ( SqlExpression | SourceField ) ( ',' Constant )? ')' WindowClauseRAQL
| 'NTH_VALUE' '(' ( SqlExpression | SourceField ) ',' S_NUMBER ( ',' Constant )? ')' WindowClauseRAQL
Used In:
SqlPrimaryExpressionFunction Call, Generic
built-in functions not covered in sql + user-defined functions
Definition:
GenericFunctionCall
::= ObjectName '(' SqlExpressionList?
( ';' ConstantList )? ')'
Used In:
SqlPrimaryExpressionGroup By Clause
Definition:
GroupByClause
::= 'GROUP' 'BY' SqlExpressionList
Used In:
SelectWithoutOrderHaving Clause
Definition:
HavingClause
::= 'HAVING' ( SetCondition | SqlExpression )
Used In:
SelectWithoutOrderS_IDENTIFIER
Definition:
S_IDENTIFIER
::= NAMESTARTCHAR NAMECHAR*
Used In:
DefineItem
ObjectName
S_UDO_IDENTIFIERS_ QUOTED_IDENTIFIER
Definition:
S_QUOTED_IDENTIFIER
::= '"' [^#x000A#x000D"]* '"'
Used In:
ObjectNameIn Condition
Definition:
SqlInClause
::= 'NOT'? 'IN' '(' SqlExpressionList ')'
Used In:
SqlRelationalExpressionINTEGER
Definition:
INTEGER ::= DIGIT+
Used In:
FLOAT
S_NUMBERIs Null Condition
Definition:
IsNullClause
::= 'IS' 'NOT'? 'NULL'
Used In:
SqlRelationalExpressionJoin Clause
Definition:
JoinedTable
::= 'CROSS' 'JOIN' SourceReferenceOrSubquery
| 'NATURAL' 'INNER'? 'JOIN' SourceReferenceOrSubquery
| 'INNER'? 'JOIN' SourceReferenceOrSubquery
( 'ON' SqlExpression
| 'USING' '(' SourceFieldList ')' )?
Used In:
FromItemLike Condition
Definition:
SqlLikeClause
::= 'NOT'? 'LIKE' SqlSimpleExpression
Used In:
SqlRelationalExpressionLimit Clause (RAQL Only)
Definition:
LimitClause
::= 'LIMIT' S_NUMBER ( ',' S_NUMBER )?
Used In:
SelectWithOrderMatching Clause (EQL Only)
Applies only to EQL queries in webMethods Business Events.
Measure Clause (EQL Only)
Applies only to EQL queries in webMethods Business Events.
Measure Item (EQL Only)
Applies only to EQL queries in webMethods Business Events.
NAMECHAR
Definition:
NAMECHAR ::= NAMESTARTCHAR
| [$0-9#x00B7#x0300-#x036F#x203F-#x2040]
Used In:
S_IDENTIFIERNAMESTARTCHAR
Definition:
NAMESTARTCHAR
::= [:A-Z_a-z#x00C0-#x00D6#x00D8-#x00F6#x00F8-#x02FF#x0370-#x037D#x037F-#x1FFF#x200C-#x200D#x2070-#x218F#x2C00-#x2FEF#x3001-#xD7FF#xF900-#xFDCF#xFDF0-#xFFFD]
Used In:
NAMECHAR
S_IDENTIFIERS_NUMBER
Definition:
S_NUMBER ::= FLOAT ( [eE] [-+]? INTEGER )?
Used In:
ArrayElementReference
Constant
FrameBound
LimitClause
MeasureItem
SetCondition
ShiftClause
WindowClauseEQL
WindowFunctionCallObject Name
Definition:
ObjectName
::= S_IDENTIFIER | S_QUOTED_IDENTIFIER
Used In:
Action
Alias
FieldDefinition
GenericFunctionCall
MeasureItem
SelectStar
SourceField
SourceReference
SqlPrimaryExpression
WithClauseOrder By Clause (RAQL Only)
Definition:
OrderByClause
::= 'ORDER' 'BY' SqlExpression
( 'ASC' | 'DESC' )?
( 'NULLS' ( 'FIRST' | 'LAST' ) )?
( ',' SqlExpression ( 'ASC' | 'DESC' )?
( 'NULLS' ( 'FIRST' | 'LAST' ) )? )*
Used In:
SelectWithOrder
WindowClauseRAQLQuery Expression Body
Definition:
QueryExpressionBody
::= QueryTerm
( ( 'UNION' | 'EXCEPT' | 'MINUS' )
( 'ALL' | 'DISTINCT' )? QueryTerm )*
Used In:
QueryPrimary
SelectWithOrder
SqlRelationalOperatorExpressionQuery Primary
Definition:
QueryPrimary
::= SelectWithoutOrder | '(' QueryExpressionBody ')'
Used In:
QueryTermQuery Term
Definition:
QueryTerm
::= QueryPrimary ( 'INTERSECT' ( 'ALL' | 'DISTINCT' )?
QueryPrimary )*
Used In:
QueryExpressionBodyRelational Operators
Definition:
Relop ::= '='
| '!='
| '#'
| '<>'
| '>'
| '>='
| '<'
| '<='
Used In:
SqlRelationalOperatorExpressionSelect Item
Definition:
SelectItem
::= '*' | SelectStar | SqlSimpleExpression Alias?
Used In:
SelectListSelect List
Definition:
SelectList
::= SelectItem ( ',' SelectItem )*
Used In:
SelectWithoutOrderSelect Wildcard
Definition:
SelectStar
::= ObjectName '.' ( ObjectName '.' )? '*'
Used In:
SelectItemSelect Statement
Definition:
SelectStatement
::= WithClause? SelectWithOrder ';'? EOF
Used In:
This is the starting definition of RAQL queries, which has no parent references.
Select With Order
Definition:
SelectWithOrder
::= QueryExpressionBody OrderByClause? LimitClause?
Used In:
SelectStatement
SourceReferenceOrSubquery
WithClauseSelect Without Order
Definition:
SelectWithoutOrder
::= 'SELECT' ( 'ALL' | 'DISTINCT' )? SelectList FromClause WhereClause? GroupByClause? HavingClause?
Used In:
QueryPrimarySet Condition (EQL Only)
Applies only to EQL queries in webMethods Business Events.
Set Condition, Multiple Field (EQL Only)
Applies only to EQL queries in webMethods Business Events.
Set Condition, Single Field (EQL Only)
Applies only to EQL queries in webMethods Business Events.
Shift Clause (EQL Only)
Applies only to EQL queries in webMethods Business Events.
Source Field
Definition:
SourceField
::= ObjectName ( '.' ObjectName ( '.' ObjectName )? )?
Used In:
AggregateFunctionCall
SingleFieldSetCondition
SourceFieldList
WindowFunctionCallSource Field List
Definition:
SourceFieldList
::= SourceField ( ',' SourceField )*
Used In:
JoinedTable
MatchingClause
MultipleFieldSetCondition
WindowClauseEQLSource Reference
Definition:
SourceReference
::= ObjectName ( '.' ObjectName )?
( '/' ObjectName ( '.' ObjectName )? )+
| UserDefinedOperator
| ObjectName ( '.'
( UserDefinedOperator | ObjectName ) )?
Used In:
SourceReferenceList
SourceReferenceOrSubquerySource Reference List (EQL Only)
Applies only to EQL queries in webMethods Business Events.
Source Reference or Subquery
Definition:
SourceReferenceOrSubquery
::= SourceReference TemporalClause Alias? MatchingClause?
| '(' SelectWithOrder ')' TemporalClause Alias? MatchingClause?
Used In:
FromItem
JoinedTableSQL Expresssion
Definition:
SqlExpression
::= SqlAndExpression ( 'OR' SqlAndExpression )*
Used In:
AggregateFunctionCall
BooleanCaseCall
DefineItem
FunctionCall
HavingClause
JoinedTable
OrderByClause
SqlExpressionList
SqlPrimaryExpression
WhereClause
WindowFunctionCallSQL Expression, And
Definition:
SqlAndExpression
::= SqlUnaryLogicalExpression ( 'AND' SqlUnaryLogicalExpression )*
Used In:
SqlExpressionSQL Expression, Exponent
Definition:
SqlExponentExpression
::= SqlUnaryExpression ( '**' SqlUnaryExpression )*
Used In:
SqlMultiplicativeExpressionSQL Expression, List
Definition:
SqlExpressionList
::= SqlExpression ( ',' SqlExpression )*
Used In:
ArrayValueConstructor
GenericFunctionCall
GroupByClause
SqlInClause
SqlRelationalExpression
WindowClauseRAQLSQL Expression, Multiplicative
Definition:
SqlMultiplicativeExpression
::= SqlExponentExpression ( ( '*' | '/' | '%' ) SqlExponentExpression )*
Used In:
SqlSimpleExpressionSql Expression, Primary
Definition:
SqlPrimaryExpression
::= '(' SqlExpression ')'
| Constant
| FunctionCall
| AggregateFunctionCall WindowClauseRAQL?
| WindowFunctionCall
| GenericFunctionCall WindowClauseRAQL?
| ObjectName ( '.'
( GenericFunctionCall WindowClauseRAQL?
| ObjectName
( '.' ObjectName ArrayElementReference?
| ArrayElementReference )? )
| ArrayElementReference )?
| ArrayValueConstructor
Used In:
SqlUnaryExpressionSQL Expression, Relational
Definition:
SqlRelationalExpression
::= ( '(' SqlExpressionList ')' | SqlSimpleExpression ) ( SqlRelationalOperatorExpression | SqlInClause | SqlBetweenClause | SqlLikeClause | IsNullClause )?
Used In:
SqlUnaryLogicalExpressionSQL Expression, Relational Operators
Definition:
SqlRelationalOperatorExpression
::= RelOp ( '(' QueryExpressionBody ')' | SqlSimpleExpression )
Used In:
SqlRelationalExpressionSQL Expression, Simple
Definition:
SqlSimpleExpression
::= SqlMultiplicativeExpression ( ( '+' | '-' | '||' ) SqlMultiplicativeExpression )*
Used In:
Action
BooleanCaseCall
MatchingClause
SelectItem
SimpleCaseCall
SqlBetweenClause
SqlLikeClause
SqlRelationalExpression
SqlRelationalOperatorExpression
WindowClauseEQLSQL Expression, Unary
Definition:
SqlUnaryExpression
::= SqlPrimaryExpression
| ( '+' | '-' ) SqlPrimaryExpression
| SqlPrimaryExpression
Used In:
SqlExponentExpressionSQL Expression, Unary Logical
Definition:
SqlUnaryLogicalExpression
::= 'NOT'? SqlRelationalExpression
Used In:
SqlAndExpressionTemporal Clause (EQL Only)
Applies only to EQL queries in webMethods Business Events.
Time Unit, Constant (EQL Only)
Applies only to EQL queries in webMethods Business Events.
Time Unit, Nonconstant (EQL Only)
Applies only to EQL queries in webMethods Business Events.
S_UDO_IDENTIFIER
Applies only to EQL queries in webMethods Business Events.
User Defined Operator (EQL Only)
Applies only to EQL queries in webMethods Business Events.
Where Clause
Definition:
WhereClause
::= 'WHERE' SqlExpression
Used In:
SelectWithoutOrderWhite Space and Other Ignorable Characters
The parser also ignores common white space characters such as space or line ends, along with comment lines that use the single-line or multi-line formats.
Definition:
Ignorable white space
::= ' '
| #x0009
| #x000D
| #x000A
| Single-line-comment
| Multi-line-comment
Single-line-comment
::= '--' [^#x000D#x000A]*
Multi-line-comment
::= '/' '*' ( [^*] | '*'+ [^*/] )* '*'+ '/'
Window Clause (EQL Only)
Applies only to EQL queries in webMethods Business Events.
Window Clause (RAQL Only)
Definition:
WindowClauseRAQL
::= 'OVER' '(' ( 'PARTITION' 'BY' SqlExpressionList )?
OrderByClause? WindowFrameClause? ')'
Used In:
SqlPrimaryExpression
WindowFunctionCallWindow Frame Boundary (RAQL Only)
Definition:
WindowFrameBound
::= ( 'UNBOUNDED' | FrameBound ) 'FOLLOWING'
| WindowFrameStart
Used In:
WindowFrameClauseWindow Frame Clause (RAQL Only)
Definition:
WindowFrameClause
::= ( 'ROWS' | 'RANGE' ) ( WindowFrameStart | 'BETWEEN' WindowFrameBound 'AND' WindowFrameBound )
Used In:
WindowClauseRAQLWindow Frame Start (RAQL Only)
Definition:
WindowFrameStart
::= ( 'UNBOUNDED' | FrameBound ) 'PRECEDING'
| 'CURRENT' 'ROW'
Used In:
WindowFrameBound
WindowFrameClauseWith Clause
Applies only to EQL queries in webMethods Business Events.