Help with select statement when WHEN Clause involving multiple databases

Question

Help with select statement when WHEN Clause involving multiple databases

skb 44459

SSCarpal Tunnel

Points: 4768
More actions
May 5, 2016 at 5:01 am

#149275

I would like to write a Select Statement, which will bring additional columns from different databases based on the value of Column.
Select
Site, Trans_num
case
When Site = 'Site1'
Select * from Site1DB.dbo.GetDetails where Site1DB.dbo.GetDetails.trans_num = GeneralTransactions.trans_num
When Site = 'Site2'
Select * from Site2DB.dbo.GetDetails where Site2DB.dbo.GetDetails.trans_num = GeneralTransactions.trans_num
When Site = 'Site3'
Select * from Site2DB.dbo.GetDetails where Site3DB.dbo.GetDetails.trans_num = GeneralTransactions.trans_num
end
From GeneralTransactions

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

skb 44459 (5/5/2016)
I would like to write a Select Statement, which will bring additional columns from different databases based on the value of Column.
Select
Site, Trans_num
case
When Site = 'Site1'
Select * from Site1DB.dbo.GetDetails where Site1DB.dbo.GetDetails.trans_num = GeneralTransactions.trans_num
When Site = 'Site2'
Select * from Site2DB.dbo.GetDetails where Site2DB.dbo.GetDetails.trans_num = GeneralTransactions.trans_num
When Site = 'Site3'
Select * from Site2DB.dbo.GetDetails where Site3DB.dbo.GetDetails.trans_num = GeneralTransactions.trans_num
end
From GeneralTransactions

You can't do it quite like that.

Instead, create a variable to hold site and populate it. Then

if @Site = 'Site1'

begin

select ... ;

end

if @Site = 'Site2'

begin

select ... ;

end

...

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

skb 44459 SSCarpal Tunnel Points: 4768 More actions · Answer 2

I don't know if I can use variables in select. I want to create a view for my select statement

Lowell SSC Guru Points: 323518 More actions · Answer 3

you probably need to simply join three groups of data together to create your view; it might be slow;

CREATE VIEW vwGeneralTransactionsMultiSite

AS

SELECT T1.Site,

T1.Trans_num,

T2.*

FROM GeneralTransactions

INNER JOIN Site1DB.dbo.GetDetails T2

ON T1.trans_num = T2.trans_num

AND T1.Site = 'Site1'

UNION ALL

SELECT T1.Site,

T1.Trans_num,

T2.*

FROM GeneralTransactions

INNER JOIN Site2DB.dbo.GetDetails T2

ON T1.trans_num = T2.trans_num

AND T1.Site = 'Site2'

UNION ALL

SELECT T1.Site,

T1.Trans_num,

T2.*

FROM GeneralTransactions

INNER JOIN Site3DB.dbo.GetDetails T2

ON T1.trans_num = T2.trans_num

AND T1.Site = 'Site3'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!