Literal Sets

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

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

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

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

In the above 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 nets out to the fact that a table can be defined from literals as:

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

The result is a table T 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 not be fully qualified. They are just qualified to the extent needed to make them unique. When generating an SQL expression for it, 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 expression.

In order to get a table T from T1, which contains the fully qualified names, you must use the following:

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

In the above query, “||” is the string concatenation operator.