BUG: SQL SELECT Query to OLAP Does Not Return Results for Certain WHERE Clauses (266777)

MORE INFORMATION

You can use an ad-hoc query tool such as the OLE DB QueryDemo sample or a Microsoft ActiveX Data Objects (ADO) application to pass a SQL query to OLAP server and get the results.

Here are a few examples:

Case 1

"SELECT DISTINCT [Customers:Country], " & _
           "[Customers:State Province], [Customers:City], " & _
           "[Customers:Name] " & _
           "FROM [Sales] " & _
           "WHERE [Customers:City] = 'Bremerton' "
				

The preceding SELECT statement returns 90 rows with the first row Customers:Name of Larry Weeks


Case 2

"SELECT DISTINCT [Customers:Country], " & _
           "[Customers:State Province], [Customers:City], " & _
           "[Customers:Name] " & _
           "FROM [Sales] " & _
           "WHERE [Customers:City] = 'Bremerton' " & _
           "AND [Customers:Name] = 'Larry Weeks' "
				

The preceding SELECT statement returns 1 row with Customers:Name of Larry Weeks


Case 3

"SELECT DISTINCT [Customers:Country], " & _
           "[Customers:State Province], [Customers:City], " & _
           "[Customers:Name] " & _
           "FROM [Sales] " & _
           "WHERE  [Customers:Name] = 'Larry Weeks' "
				

The preceding SELECT statement returns 0 rows instead of 1 row like the preceding case 2.

If you examine all the preceding statements and the cube dimensions, you will notice the following:

• Customers:Name is the lowest level in the Customers dimension and is composed of two fields ("customers"."fname" '+"customers"."lname") from the Customers table. When you specify the lowest level of a dimension, which is composed of two fields from the source table in the WHERE clause with no other clause, the SELECT query does not return any records.
  
  
• Store:Store Name is the lowest level in the Store dimension and it is only a single field ("store"."store_name") from the store table. It works ok in the WHERE clause ("WHERE [Store:Store Name] = 'Store 13' " returns 474 rows).