Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Supported SQL SELECT Syntax

SQL Server 2000

Microsoft® SQL Server™ 2000 Analysis Services supports the following subset of the SQL SELECT command 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.


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.
© 2015 Microsoft