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

• Phil Parkin

  SSC Guru

  Points: 247154

  More actions

  May 5, 2016 at 5:08 am

  #1876792

  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

  ...

  ---

• skb 44459

  SSCarpal Tunnel

  Points: 4768

  More actions

  May 5, 2016 at 5:21 am

  #1876793

  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: 323522

  More actions

  May 5, 2016 at 5:27 am

  #1876795

  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!