?
Solved

SQL CASE

Posted on 2012-08-22
6
Medium Priority
?
417 Views
Last Modified: 2012-08-24
This is driving me nuts. I have the following code:

SELECT     dbo.ITX_vw_tbl_Patient.UnosId
FROM         dbo.ITX_vw_tbl_Patient INNER JOIN
                      dbo.ITX_vw_tbl_DonorReferral ON dbo.ITX_vw_tbl_Patient.Id = dbo.ITX_vw_tbl_DonorReferral.PatientId INNER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue ON dbo.ITX_vw_tbl_DonorReferral.OrganOutcomeDetail = dbo.ITX_vw_tbl_EnumerationValue.Id
GROUP BY dbo.ITX_vw_tbl_Patient.UnosId

It returns the UnosId. The problem is I need to return multiple records related to this UnosId from dbo.ITX_vw_tbl_EnumerationValue and list them as columns. I tried doing a CASE but it wasn't returning any values.
0
Comment
Question by:LCNW
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38322059
please post some data you have and get, and what you should get.

I presume you need the GROUP BY, and a MAX() around your case statement(s) .
0
 
LVL 1

Author Comment

by:LCNW
ID: 38322080
This code:

SELECT     dbo.ITX_vw_tbl_Patient.UnosId, MAX(CASE WHEN EnumerationTypeId = 'd9510b51-3677-4c39-b76a-eea698f50bc0' THEN Description END) AS 'Race',
                      MAX(CASE WHEN EnumerationTypeId = 'bbe49dfa-36c4-4dc4-ab24-161b66111709' THEN Description END) AS Outcome
FROM         dbo.ITX_vw_tbl_Patient INNER JOIN
                      dbo.ITX_vw_tbl_DonorReferral ON dbo.ITX_vw_tbl_Patient.Id = dbo.ITX_vw_tbl_DonorReferral.PatientId INNER JOIN
                      dbo.ITX_vw_tbl_EnumerationValue ON dbo.ITX_vw_tbl_DonorReferral.OrganOutcomeDetail = dbo.ITX_vw_tbl_EnumerationValue.Id
GROUP BY dbo.ITX_vw_tbl_Patient.UnosId

Returns:

VG1073      NULL      NULL
XFZ496      NULL      NULL
XJB371      NULL      NULL
VHB046      NULL      NULL
YGG243      NULL      NULL
ZD1218      NULL      NULL
YLY223      NULL      NULL
VKW277      NULL      NULL
ZFL099      NULL      NULL

I know those EnumerationIds exist. I can filter against the table directly and get a matches.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38322135
if you get NULL's there, the CASE does not find a match.
it's as simple as that.

I know you posted already a question around this kind of query, so the issue isn't there.
I can only presume that some of the JOINS make that there is indeed no such row returned.
please double-check the data/join conditions etc to find the issue.

debug without the GROUP BY and without the MAX() etc to find where the issue is
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 1

Author Comment

by:LCNW
ID: 38322740
How could I list these 2 Ids side-by-side?

SELECT     EnumerationTypeId, Description
FROM         dbo.ITX_vw_tbl_EnumerationValue
WHERE     (EnumerationTypeId = 'd9510b51-3677-4c39-b76a-eea698f50bc0') OR
                      (EnumerationTypeId = 'bbe49dfa-36c4-4dc4-ab24-161b66111709')

Result is:

bbe49dfa-36c4-4dc4-ab24-161b66111709      SCD
bbe49dfa-36c4-4dc4-ab24-161b66111709      ECD
bbe49dfa-36c4-4dc4-ab24-161b66111709      DCD
d9510b51-3677-4c39-b76a-eea698f50bc0      Native Hawaiian or Other Pacific Islander: Guamanian or Chamorro
d9510b51-3677-4c39-b76a-eea698f50bc0      Hispanic/Latino: Cuban
d9510b51-3677-4c39-b76a-eea698f50bc0      Native Hawaiian or Other Pacific Islander: Native Hawaiian
d9510b51-3677-4c39-b76a-eea698f50bc0      Black or African American: African American
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Korean
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Asian Indian/Indian Sub-Continent
d9510b51-3677-4c39-b76a-eea698f50bc0      White: European Descent
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Not Specified/Unknown
d9510b51-3677-4c39-b76a-eea698f50bc0      American Indian or Alaska Native: Not Specified/Unknown
d9510b51-3677-4c39-b76a-eea698f50bc0      White: Arab or Middle Eastern
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Other
d9510b51-3677-4c39-b76a-eea698f50bc0      Hispanic/Latino: Puerto Rican (Island)
d9510b51-3677-4c39-b76a-eea698f50bc0      Black or African American: West Indian
d9510b51-3677-4c39-b76a-eea698f50bc0      American Indian or Alaska Native: Other
d9510b51-3677-4c39-b76a-eea698f50bc0      White: Other
d9510b51-3677-4c39-b76a-eea698f50bc0      Black or African American: Haitian
d9510b51-3677-4c39-b76a-eea698f50bc0      Hispanic/Latino: Mexican
d9510b51-3677-4c39-b76a-eea698f50bc0      American Indian or Alaska Native: Alaska Indian
d9510b51-3677-4c39-b76a-eea698f50bc0      Black or African American: African (Continental)
d9510b51-3677-4c39-b76a-eea698f50bc0      Native Hawaiian or Other Pacific Islander: Samoan
d9510b51-3677-4c39-b76a-eea698f50bc0      White: North African (non-Black)
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Chinese
d9510b51-3677-4c39-b76a-eea698f50bc0      Hispanic/Latino: Other
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Vietnamese
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Filipino
d9510b51-3677-4c39-b76a-eea698f50bc0      Native Hawaiian or Other Pacific Islander: Other
d9510b51-3677-4c39-b76a-eea698f50bc0      Black or African American: Not Specified/Unknown
d9510b51-3677-4c39-b76a-eea698f50bc0      Hispanic/Latino: Puerto Rican (Mainland)
d9510b51-3677-4c39-b76a-eea698f50bc0      Hispanic/Latino: Not Specified/Unknown
d9510b51-3677-4c39-b76a-eea698f50bc0      White: Not Specified/Unknown
d9510b51-3677-4c39-b76a-eea698f50bc0      Asian: Japanese
d9510b51-3677-4c39-b76a-eea698f50bc0      American Indian or Alaska Native: Eskimo
d9510b51-3677-4c39-b76a-eea698f50bc0      American Indian or Alaska Native: American Indian
d9510b51-3677-4c39-b76a-eea698f50bc0      Native Hawaiian or Other Pacific Islander: Not Specified/Unknown
d9510b51-3677-4c39-b76a-eea698f50bc0      American Indian or Alaska Native: Aleutian
d9510b51-3677-4c39-b76a-eea698f50bc0      Black or African American: Other
NULL      NULL
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38322784
you are missing here the "link" column that tells which 2 rows are to be "merged" ...

say it's FK column

SELECT     FK
, MAX( CASE WHEN EnumerationTypeId = 'd9510b51-3677-4c39-b76a-eea698f50bc0' THEN Description END) x1
, MAX( CASE WHEN EnumerationTypeId = 'bbe49dfa-36c4-4dc4-ab24-161b66111709' THEN Description END) x2
FROM         dbo.ITX_vw_tbl_EnumerationValue
WHERE   EnumerationTypeId IN ( 'd9510b51-3677-4c39-b76a-eea698f50bc0', 'bbe49dfa-36c4-4dc4-ab24-161b66111709')
GROUP BY FK

Open in new window

0
 
LVL 1

Author Comment

by:LCNW
ID: 38327166
Okay I'm going to try and explain this one more time, starting with logic. I have a PatientID table and an EnumerationValue Table. Here are their columns:

PatientID:
Id      Age      Sex     Weight     Height    Race

EnumerationValue:
Id     Description     Value

The only way I have found to link these tables is through the Race>Value column. Problem is, I need to get additional data out of the EnumerationValue table once joined. I need some other values for the same PatientID and I'm not sure how to get those additional values as the join locks me down to race related info.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question