Supported SQL SELECT Syntax

Microsoft® SQL Server™ 2000 Analysis Services supports the following subset of the SQL SELECT command syntax.

Syntax

SELECT [<options_clause>] <select_list> FROM <from_clause> [WHERE <where_clause>] [GROUP BY <groupby_clause>]

<options_clause> ::= <empty_clause> | DISTINCT

<select_list> ::= <scalar_exp_commalist> | ASTERISK

<scalar_exp_commalist> ::= <scalar_expression> [, <scalar_expression> [, <scalar_expression> [...]]]

<scalar_expression> ::= <column_ref>

    | <aggregate>

    | (<column_ref>) AS IDENTIFIER

<aggregate> ::= <aggregate_func> (<column_ref>)

Note  In the preceding line, <column_ref> must be a measure name. <aggregate_func> must agree with the Aggregate Function property of the measure.

<aggregate_func> ::= COUNT | MIN | MAX| SUM

Note  <select_list> can contain references only to levels or measures. If measures are specified, you must also specify <aggregate>.

<from_clause> ::= cube_name

<where_clause> ::= empty_clause | <search_condition>

<groupby_clause> ::= <column_ref_commalist>

<column_ref_commalist> ::= (<column_ref>) [, (<column_ref>) [, (<column_ref>) [...]]]

<search_condition> ::= <empty_clause>

    | <search_condition> AND <search_condition>

    | <search_condition> OR <search_condition>

    | (<search_condition>)

    | <comparison_predicate>

<comparison_predicate> ::= (<column_ref>) = VALUE | VALUE = (<column_ref>)

Note  In the preceding line, <column_ref> must be a valid level name.

Remarks

There are some limitations of the SQL SELECT statement in Analysis Services.

• You cannot use DISTINCT or GROUP BY if <select_list> contains members.
  
  
• Using the DISTINCT option with levels in <select_list> can cause the following problems:
  • If a parent level has more than one member, and not all parents are listed, Analysis Services may return duplicate rows. Be sure to explicitly include all parents.
    
    
  • If the root level for a dimension contains more than one member, Analysis Services may return duplicate rows. Include all dimensions with root levels having more than one member as columns.
• DISTINCT and GROUP BY may return multiple rows if the server contains more than one segment.
  
  
• You cannot use SQL syntax to query a virtual dimension that was created in SQL Server version 7.0 OLAP Services. You must use Multidimensional Expressions (MDX) to query this type of virtual dimension.