Casing Select * Statements - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Casing Select * Statements

Author

Topic

lhrit
Starting Member

1 Post

Posted - 2012-11-09 : 11:01:44

Hi all,
I'm trying to find a way of nesting 6 identical queries (connecting to 6 databases) into a CASE statement.

At the moment my qorkign query is set up like this.

Select A, D, C
from DB1.Table1
union all
Select A, D, C
from DB2.Table1
union all
Select A, D, C
from DB3.Table1;

(In real terms I have some Collation in there as well).

What I want to do is this.

DECLARE @Area Varchar(2)
SET @Area = 'UK'

CASE WHEN @Area = 'UK' THEN
Select A, D, C from DB1.Table1
ELSE
CASE WHEN @Area = 'US'
Select A, D, C from DB2.Table1
ELSE
CASE WHEN @Area = 'ES'
Select A, D, C from DB3.Table1
ELSE
Select null, null, null from BD1.Table1
END
END
END

When I try this I get the following error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I have found one way of doing this, but I don't like it, as it means putting a CASE statement for every column.

DECLARE @Area Varchar(2)

Select @Area = 'UK'

SELECT
CASE WHEN @Area = 'UK' THEN
(select A from DB1.Table1)
ELSE
(select A from DB2.Table1)
END,
CASE WHEN @Area = 'UK' THEN
(select B from DB1.Table1)
ELSE
(select B from DB2.Table1)
END;

Does anyone know of a way to get the first method to work, so I only have to end up with 5 CASE statements.

I have 6 fields to pull out and 6 databaseas to query (over 26 store proceedures in all).

Any help is appreciated

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-09 : 14:59:37

Looks like this should have been designed as one dB with an [area] column in Table1. That way it is one simple query.

But if you can't redesign then how about this:


Select A, D, C from DB1.Table1 where @area = 'UK'  
union all
Select A, D, C from DB2.Table1 where @area = 'US'  
union all
Select A, D, C from DB3.Table1 where @area = 'ES'

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources