Literal Sets

OLE DB Programmer's Reference

Literal set, as used here, denotes a set that is specified by an enumerated list of tuples. Consider the following set:

{(x,y), (a,b)}

In this set, x and a are members of dimension Name1, and y and b belong to dimension Name2. This literal set can be expanded into a table value expression such as the following:

CREATE LOCAL TEMPORARY VIEW S1(Name1, Name2, Rank) AS SELECT Name1, Name2 FROM ( VALUES (x,y,1), (a,b, 2) ) AS S2(Name1, Name2, Rank) ORDER BY Rank

In the preceding statement, the FROM clause contains a table specification, which is in the form of an enumerated list of rows. The SQL-92 <table_value_constructor> syntax has been used for this purpose.

The detailed syntax, derived from SQL-92, is as follows:

<table_reference> ::= <derived_table> [AS] <correlation_name>    [<left_paren> <derived_column_list> <right_paren> ] | ... <derived_table> ::= <table_subquery> <table_subquery> ::= ( <simple_table> | ... ) <simple_table> ::= <table_value_constructor> |... <table_value_constructor> ::=    VALUES <row_value_constructor> [ {, <row_value_constructor>} ... ] <row_value_constructor> = ::=    ( <value_expression> [ {, <value_expression> ... ] ) | ...

This means that a table can be defined from literals as follows:

<literal_table> ::= (VALUES(<literal>[{, <literal>}...)                      [{,( <literal> [ {,<literal>}...)}...] )                      [AS] <correlation_name>[ <left_paren>                           <derived_column_list> <right_paren> ]

The result is a table S for which each row is a tuple of the input set. OLE DB for OLAP requires that the member names x, y, z, a, b, and c be unambiguous names. Unambiguous names need to be qualified only to the extent needed to make them unique. When generating an SQL expression for the table, the expression must return fully qualified names for these members. This is required because the MEMBER table has fully qualified names, and it does equijoins with this table while evaluating an MDX statement.

In order to get a table S from S1, which contains the fully qualified names, use the following construct:

CREATE LOCAL TEMPORARY VIEW S(Name1, Name2, Rank) AS SELECT M1.Name1, M2.Name2, S1.Rank FROM MEMBER M1, MEMBER M2, S1 WHERE M1.Name LIKE ("%." || S.Name1) AND M2.Name LIKE ("%." || S.Name2) ORDER BY S1.Rank

In the above query, "||" is the string concatenation operator.

1998-2001 Microsoft Corporation. All rights reserved.