Microsoft KB Archive/266777

= BUG: SQL SELECT Query to OLAP Does Not Return Results for Certain WHERE Clauses =

Article ID: 266777

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server OLAP Services

-



This article was previously published under Q266777



BUG #: 9744 (Plato7x)



SYMPTOMS
A SQL SELECT query to a Microsoft OLAP 7.0 Server, using the Microsoft OLE DB Provider for OLAP, might not return results when you specify the lowest level of a dimension in the WHERE clause if the level is composed of two fields from the source table.

For example, if you query against the OLAP 7.0 FoodMart Sales database and you specify Customers Name, which is the lowest level in the Customers dimension and is composed of two fields (&quot;customers&quot;.&quot;fname&quot;+&quot;customers&quot;.&quot;lname&quot;), a result set is not produced.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This is not an issue in SQL Server 2000 Analysis Services 8.0.



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

&quot;SELECT DISTINCT [Customers:Country], &quot; & _ &quot;[Customers:State Province], [Customers:City], &quot; & _ &quot;[Customers:Name] &quot; & _ &quot;FROM [Sales] &quot; & _ &quot;WHERE [Customers:City] = 'Bremerton' &quot; The preceding SELECT statement returns 90 rows with the first row Customers:Name of Larry Weeks

Case 2 &quot;SELECT DISTINCT [Customers:Country], &quot; & _ &quot;[Customers:State Province], [Customers:City], &quot; & _ &quot;[Customers:Name] &quot; & _ &quot;FROM [Sales] &quot; & _ &quot;WHERE [Customers:City] = 'Bremerton' &quot; & _ &quot;AND [Customers:Name] = 'Larry Weeks' &quot; The preceding SELECT statement returns 1 row with Customers:Name of Larry Weeks

Case 3 &quot;SELECT DISTINCT [Customers:Country], &quot; & _ &quot;[Customers:State Province], [Customers:City], &quot; & _ &quot;[Customers:Name] &quot; & _ &quot;FROM [Sales] &quot; & _ &quot;WHERE [Customers:Name] = 'Larry Weeks' &quot; 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 (&quot;customers&quot;.&quot;fname&quot; '+&quot;customers&quot;.&quot;lname&quot;) 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 (&quot;store&quot;.&quot;store_name&quot;) from the store table. It works ok in the WHERE clause (&quot;WHERE [Store:Store Name] = 'Store 13' &quot; returns 474 rows).

Keywords: kbbug kbnofix KB266777

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.