Below is the current SQL grammar implemented in AnalytiX-BI Server, following Transact-SQL Syntax Conventions (Transact-SQL).
Be sure to read the Additional Notes below the SQL grammar examples below.
<query> ::= { <select_query> | <pivot_query> }
<select_query> ::= SELECT [ DISTINCT ] [ TOP n ] <select_list> [ FROM <table_list> ] [ WHERE <search_condition> ] [ HAVING <search_condition> ] [ ORDER BY <order_by_expression> ]
<select_list> ::= { <aliased_column> | <explicit_expression> } [,…n]
<aliased_column> ::= <column_source> [ AS <identifier> ]
<column_source> ::= { <aggregate_column> | <qualified_identifier> }
<aggregate_column> ::= <aggregate_name> ( <qualified_identifier> )
<qualified_identifier> ::= <identifier> . <identifier>
<explicit_expression> ::= ‘<free_literal>’AS <identifier>
<table_list> ::= <qualified_identifier> [ { INNER JOIN <qualified_identifier> ON <qualified_identifier> = <qualified_identifier> } [,…n] ]
<search_condition> ::= <binary_expression>
<binary_expression> ::= <expression> <binary_operator> <expression>
<binary_operator> ::= AND | OR | IN | LIKE | NOT LIKE | = | > | < | >= | <= | <> | !=
<expression> ::= <term> | ( <constants_list> ) | <binary_expression>
<term> ::= <column_source> | <identifier> | <string_literal> | <number>
<constants_list> ::= { <string_literal> | <number> } [,…n]
<string_literal> ::= ‘<string>’
<order_by_expression> ::= { { <column_source> | <identifier> } [ ASC | DESC ] } [,…n]
<pivot_query> ::= PIVOT <aggregate_column> FOR <qualified_identifier> IN ( <pivot_column_list> ) [ GROUP BY <group_by_expression> ] [ ORDER BY <pivot_order_by_expression> ]
<pivot_column_list> ::= { <string_literal> [ AS <identifier> ] } [,…n]
<group_by_expression> ::= <qualified_identifier> [,…n]
<pivot_order_by_expression> ::= { <qualified_identifier> [ ASC | DESC ] } [,…n]
The above grammar specifies the SQL syntax only. All points (which includes view definitions) must be specified with the following format (still using the pseudo-TSQL syntax as above):
bi: Models [ /<identifier> [,…n] ] : <identifier> ( <query> )
Identifiers that contain reserved symbols (spaces, commas, periods, etc.) need to be enclosed in square brackets. For example:
bi:Models:Northwind(SELECT [Order Details].OrderID)
bi:Models:Northwind(SELECT Orders.[OrderDate.Year])
See Also:
AnalytiX-BI Server SQL Queries