Data Warehousing and Business Intelligence

24 November 2010

SQL Parsing in BIDS

Filed under: Analysis Services — Vincent Rainardi @ 12:09 am

BIDS and SSMS have differences in terms of SQL parsing. In programming languages, to “parse” a statement is to break the sentence into the smallest components (called token) and check if all the tokens a) are meaningful symbol and b) are forming an allowable expression. A SQL query that runs in SSMS might not run in BIDS.

In SSMS the following SQL runs successfully:

SELECT Table4.Col1 C1, Table2.Col2 C2
FROM Table2
INNER JOIN Table3 ON Table2.Col3 = Table3.Col4
INNER JOIN Table4 ON Table3.Col2 = Table4.Col1
INNER JOIN Table5 ON Table4.Col7 = Table5.Col7
UNION
SELECT Table4.Col1, Table2.Col2
FROM Table4
INNER JOIN Table5 ON Table4.Col7 = Table5.Col7
INNER JOIN Table2 ON Table4.Col8 = Table2.Col2

But when we paste that SQL onto BIDS’s DSV and run it, BIDS changes the SQL to:

SELECT Table4.Col1 C1, Table2.Col2 C2
FROM Table2
INNER JOIN Table3 ON Table2.Col3 = Table3.Col4
INNER JOIN Table4 ON Table3.Col2 = Table4.Col1
INNER JOIN Table5 ON Table4.Col7 = Table5.Col7
UNION
SELECT Table4.Col1, Table2.Col2
FROM Table4 AS Table4_1
INNER JOIN Table5 AS Table5_1 ON Table4.Col7 = Table5.Col7
INNER JOIN Table2 AS Table2_1 ON Table4.Col8 = Table2.Col2

And gives this error message:

Multi-part identifier Table4.Col1, Table2.Col2, Table4.Col7, Table5.Col7, Table4.Col8, Table2.Col2 could not be found.

This was because BIDS detected that Table4, Table5 and Table2 were already mentioned on the first part of the query (before the union). So for the 2nd part of the query (after the union) BIDS added table aliases to Table4, Table5 and Table2 to make them unique. BIDS adds “_1” to the table name. So Table4 is now known as Table4_1, Table5 is now known as Table5_1 and Table2 is now known as Table2_1. And once you have given an alias, you need to refer the table using the alias. You can’t refer them using the original name.

The solution is adding “_1” to the multi-part identifier after the union:

SELECT Table4.Col1 C1, Table2.Col2 C2
FROM Table2
INNER JOIN Table3 ON Table2.Col3 = Table3.Col4
INNER JOIN Table4 ON Table3.Col2 = Table4.Col1
INNER JOIN Table5 ON Table4.Col7 = Table5.Col7
UNION
SELECT Table4_1.Col1, Table2_1.Col2
FROM Table4 AS Table4_1
INNER JOIN Table5 AS Table5_1 ON Table4_1.Col7 = Table5_1.Col7
INNER JOIN Table2 AS Table2_1 ON Table4_1.Col8 = Table2_1.Col2

And that runs OK in BIDS.

Note: in a union you don’t need to give an alias to the selected column names, but it’s better to do so:

SELECT Table4.Col1 C1, Table2.Col2 C2
FROM …
UNION
SELECT Table4_1.Col1 C1, Table2_1.Col2 C2
FROM …

If you name the columns before and after the UNION differently, SQL will use column names before the UNION.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: