Multiple Instance Query Using CASE

Question

Multiple Instance Query Using CASE

pawana.paul

SSCommitted

Points: 1686
More actions
August 31, 2012 at 10:33 am

#261320

Hi,
Need your help in achieving one requirement, below are the details:
From Instance1, DB1, I am retrieving data from 6-8 tables using joins.
Form Instance2, DB2, I am retrieving data from 3-4 tables using CASE function as I need to display single column data in multiple columns based on category.
I have configured linked server between Instance1 and Instance2. I can join both the queries from Inst1 and Inst2 as there are common columns which can be joined. The only concern is CASE function.
Please let me know how to join these 2 and make a single statement or block.
Thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

pawana.paul (8/31/2012)
Hi,
Need your help in achieving one requirement, below are the details:
From Instance1, DB1, I am retrieving data from 6-8 tables using joins.
Form Instance2, DB2, I am retrieving data from 3-4 tables using CASE function as I need to display single column data in multiple columns based on category.
I have configured linked server between Instance1 and Instance2. I can join both the queries from Inst1 and Inst2 as there are common columns which can be joined. The only concern is CASE function.
Please let me know how to join these 2 and make a single statement or block.
Thanks in advance

You have to give us something to work with. I don't think we need full ddl but at least something. Can you at least post the queries you are running?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pawana.paul SSCommitted Points: 1686 More actions · Answer 2

Thanks for your response. Below are the statements I am running

On Instance1, it is something similar to the below statement:

SELECT A.COL1, B.COL2,C.COL3, A.COL4, B.COL5, C.COL6 FROM

ABC A JOIN DEF B ON A.CODE = B.CODE

JOIN XYZ C ON B.EMP_ID = C.EMP_ID WHERE A.JOIN_DATE = 'DATE'

On Instance2, similar to the below statement:

SELECT

CASE WHEN D.COL1 = 1 THEN E.NAME ELSE ' ' END [INVEST1],

CASE WHEN D.COL1 = 2 THEN E.NAME ELSE ' ' END [INVEST2],

CASE WHEN D.COL1 = 3 THEN E.NAME ELSE ' ' END [INVEST3]

FROM XYZ D JOIN IJK E ON D.COL1 = E.CODE WHERE E.COL1 IN [1,2,3] AND E.DATE_CREATED = 'DATE'

Thanks in advance,

pawana.paul SSCommitted Points: 1686 More actions · Answer 3

adding to the below email.

I have linked server configured between instance1 and instance2. In 2nd select statement, I am going to use [Instance.database.dbo.table] in the case statement. As the queries is written separately on both the instances, I have not used [instance.database.dbo.table]

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

pawana.paul (8/31/2012)
Thanks for your response. Below are the statements I am running
On Instance1, it is something similar to the below statement:
SELECT A.COL1, B.COL2,C.COL3, A.COL4, B.COL5, C.COL6 FROM
ABC A JOIN DEF B ON A.CODE = B.CODE
JOIN XYZ C ON B.EMP_ID = C.EMP_ID WHERE A.JOIN_DATE = 'DATE'
On Instance2, similar to the below statement:
SELECT
CASE WHEN D.COL1 = 1 THEN E.NAME ELSE ' ' END [INVEST1],
CASE WHEN D.COL1 = 2 THEN E.NAME ELSE ' ' END [INVEST2],
CASE WHEN D.COL1 = 3 THEN E.NAME ELSE ' ' END [INVEST3]
FROM XYZ D JOIN IJK E ON D.COL1 = E.CODE WHERE E.COL1 IN [1,2,3] AND E.DATE_CREATED = 'DATE'
Thanks in advance,

OK now I am a little confused as to what you are trying to do. Originally you said something about a UNION but these don't have the same columns. Can you try to explain clearly what you are trying to do or what the issue is that you are running in to?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pawana.paul SSCommitted Points: 1686 More actions · Answer 5

Hi,

I don't want to UNION here instead I want to get the output from both the query in single query. I want to add both query into one so that I can get the required output. the result should be something like below

A.COL1 B.COL2 C.COL3 A.COL4 B.COL5 C.COL6 INVEST1 INVEST2 INVEST3 (COL1 TO COL6 FROM INSTANCE1 AND INVEST 1 -3 FROM INSTANCE2)

pawana.paul SSCommitted Points: 1686 More actions · Answer 6

Thanks...

When trying at my end, the below query worked for me...

SELECT A.COL1, B.COL2,C.COL3, A.COL4, B.COL5, C.COL6,

CASE WHEN D.COL1 = 1 THEN E.NAME ELSE ' ' END [INVEST1],

CASE WHEN D.COL1 = 2 THEN E.NAME ELSE ' ' END [INVEST2],

CASE WHEN D.COL1 = 3 THEN E.NAME ELSE ' ' END [INVEST3]

FROM ABC A JOIN DEF B ON A.CODE = B.CODE

JOIN XYZ C ON B.EMP_ID = C.EMP_ID WHERE A.JOIN_DATE = 'DATE'

JOIN [INSTANCENAME].DBNAME.DBO.XYZ D ON D.CODE = A.CODE

AND FEW MORE CONDITIONS

Thanks very much.

Sean Lange SSC Guru Points: 286573 More actions · Answer 7

So you managed to figure out what you needed? Not sure what I did other than try to understand the question but you are welcome. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pawana.paul SSCommitted Points: 1686 More actions · Answer 8

Yes. Right now I could able to figure out.

I may need your help in the near futher. Right now I am not clear about the requirement. Once I have that, if I need help, I will reach you out.

Regards,