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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Casing Select * Statements
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lhrit
Starting Member

1 Posts

Posted - 11/09/2012 :  11:01:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6065 Posts

Posted - 11/09/2012 :  14:59:37  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000