SQL Server Forums
Profile | Register | 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		  New Topic  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
5469 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  
 New 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