?
Solved

SQL CASE

Posted on 2012-08-20
15
Medium Priority
?
645 Views
Last Modified: 2012-08-20
I have a simple CASE statement that looks for a GUID value:

SELECT Abbreviation, Value, CASE WHEN EnumerationTypeId = 'ebc459c9-b431-4385-b123-2f239dfd02cd' THEN Description ELSE NULL
                      END AS Details
FROM        REPLICATION.dbo.EnumerationValue

I know the GUID exists. Why cant my CASE find a match?

Thanks.
0
Comment
Question by:LCNW
  • 8
  • 7
15 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38314063
can you show the result of the EnumerationTypeid value from that query?
also, what is the data type of the column?
0
 
LVL 1

Author Comment

by:LCNW
ID: 38314071
It's a uniqueidentifier. If I do:

SELECT     EnumerationTypeId, Description
FROM         dbo.ITX_vw_tbl_EnumerationValue
WHERE     (EnumerationTypeId = 'ebc459c9-b431-4385-b123-2f239dfd02cd')

I get several records:
ebc459c9-b431-4385-b123-2f239dfd02cd      White: European Descent
ebc459c9-b431-4385-b123-2f239dfd02cd      American Indian or Alaska Native: Other
ebc459c9-b431-4385-b123-2f239dfd02cd      Hispanic/Latino: Cuban
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Japanese
ebc459c9-b431-4385-b123-2f239dfd02cd      Hispanic/Latino: Other
ebc459c9-b431-4385-b123-2f239dfd02cd      Native Hawaiian or Other Pacific Islander: Not Specified/Unknown
ebc459c9-b431-4385-b123-2f239dfd02cd      White: North African (non-Black)
ebc459c9-b431-4385-b123-2f239dfd02cd      Native Hawaiian or Other Pacific Islander: Samoan
ebc459c9-b431-4385-b123-2f239dfd02cd      Hispanic/Latino: Puerto Rican (Mainland)
ebc459c9-b431-4385-b123-2f239dfd02cd      Native Hawaiian or Other Pacific Islander: Other
ebc459c9-b431-4385-b123-2f239dfd02cd      Native Hawaiian or Other Pacific Islander: Guamanian or Chamorro
ebc459c9-b431-4385-b123-2f239dfd02cd      Black or African American: West Indian
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Other
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Asian Indian/Indian Sub-Continent
ebc459c9-b431-4385-b123-2f239dfd02cd      American Indian or Alaska Native: Aleutian
ebc459c9-b431-4385-b123-2f239dfd02cd      American Indian or Alaska Native: Eskimo
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Vietnamese
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Not Specified/Unknown
ebc459c9-b431-4385-b123-2f239dfd02cd      Hispanic/Latino: Not Specified/Unknown
ebc459c9-b431-4385-b123-2f239dfd02cd      White: Not Specified/Unknown
ebc459c9-b431-4385-b123-2f239dfd02cd      Black or African American: Not Specified/Unknown
ebc459c9-b431-4385-b123-2f239dfd02cd      American Indian or Alaska Native: Alaska Indian
ebc459c9-b431-4385-b123-2f239dfd02cd      Native Hawaiian or Other Pacific Islander: Native Hawaiian
ebc459c9-b431-4385-b123-2f239dfd02cd      Black or African American: Other
ebc459c9-b431-4385-b123-2f239dfd02cd      White: Other
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Chinese
ebc459c9-b431-4385-b123-2f239dfd02cd      American Indian or Alaska Native: Not Specified/Unknown
ebc459c9-b431-4385-b123-2f239dfd02cd      Black or African American: African American
ebc459c9-b431-4385-b123-2f239dfd02cd      Black or African American: African (Continental)
ebc459c9-b431-4385-b123-2f239dfd02cd      Black or African American: Haitian
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Korean
ebc459c9-b431-4385-b123-2f239dfd02cd      White: Arab or Middle Eastern
ebc459c9-b431-4385-b123-2f239dfd02cd      Hispanic/Latino: Puerto Rican (Island)
ebc459c9-b431-4385-b123-2f239dfd02cd      American Indian or Alaska Native: American Indian
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Filipino
ebc459c9-b431-4385-b123-2f239dfd02cd      Hispanic/Latino: Mexican
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38314097
well, then your CASE statement will also work ...
unless you have a typo somewhere or the like.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 1

Author Comment

by:LCNW
ID: 38314101
It's returning nulls
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38314109
you must be "hiding" something here.
how exactly do you run the query/case statement?

what does this return:
SELECT     EnumerationTypeId, Description
, CASE WHEN EnumerationTypeId =  'ebc459c9-b431-4385-b123-2f239dfd02cd' THEN 'A' ELSE 'B' END AS Check
, CASE WHEN EnumerationTypeId =  'ebc459c9-b431-4385-b123-2f239dfd02cd' THEN Description END AS Check
FROM         dbo.ITX_vw_tbl_EnumerationValue
WHERE     (EnumerationTypeId = 'ebc459c9-b431-4385-b123-2f239dfd02cd')

Open in new window

0
 
LVL 1

Author Comment

by:LCNW
ID: 38314131
If I add the WHERE it gets the results. Why do I need the WHERE?

ebc459c9-b431-4385-b123-2f239dfd02cd      White: European Descent      A      White: European Descent
ebc459c9-b431-4385-b123-2f239dfd02cd      American Indian or Alaska Native: Other      A      American Indian or Alaska Native: Other
ebc459c9-b431-4385-b123-2f239dfd02cd      Hispanic/Latino: Cuban      A      Hispanic/Latino: Cuban
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Japanese      A      Asian: Japanese
ebc459c9-b431-4385-b123-2f239dfd02cd      Hispanic/Latino: Other      A      Hispanic/Latino: Other
ebc459c9-b431-4385-b123-2f239dfd02cd      Native Hawaiian or Other Pacific Islander: Not Specified/Unknown      A      Native Hawaiian or Other Pacific Islander: Not Specified/Unknown
ebc459c9-b431-4385-b123-2f239dfd02cd      White: North African (non-Black)      A      White: North African (non-Black)
ebc459c9-b431-4385-b123-2f239dfd02cd      Native Hawaiian or Other Pacific Islander: Samoan      A      Native Hawaiian or Other Pacific Islander: Samoan
ebc459c9-b431-4385-b123-2f239dfd02cd      Hispanic/Latino: Puerto Rican (Mainland)      A      Hispanic/Latino: Puerto Rican (Mainland)
ebc459c9-b431-4385-b123-2f239dfd02cd      Native Hawaiian or Other Pacific Islander: Other      A      Native Hawaiian or Other Pacific Islander: Other
ebc459c9-b431-4385-b123-2f239dfd02cd      Native Hawaiian or Other Pacific Islander: Guamanian or Chamorro      A      Native Hawaiian or Other Pacific Islander: Guamanian or Chamorro
ebc459c9-b431-4385-b123-2f239dfd02cd      Black or African American: West Indian      A      Black or African American: West Indian
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Other      A      Asian: Other
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Asian Indian/Indian Sub-Continent      A      Asian: Asian Indian/Indian Sub-Continent
ebc459c9-b431-4385-b123-2f239dfd02cd      American Indian or Alaska Native: Aleutian      A      American Indian or Alaska Native: Aleutian
ebc459c9-b431-4385-b123-2f239dfd02cd      American Indian or Alaska Native: Eskimo      A      American Indian or Alaska Native: Eskimo
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Vietnamese      A      Asian: Vietnamese
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Not Specified/Unknown      A      Asian: Not Specified/Unknown
ebc459c9-b431-4385-b123-2f239dfd02cd      Hispanic/Latino: Not Specified/Unknown      A      Hispanic/Latino: Not Specified/Unknown
ebc459c9-b431-4385-b123-2f239dfd02cd      White: Not Specified/Unknown      A      White: Not Specified/Unknown
ebc459c9-b431-4385-b123-2f239dfd02cd      Black or African American: Not Specified/Unknown      A      Black or African American: Not Specified/Unknown
ebc459c9-b431-4385-b123-2f239dfd02cd      American Indian or Alaska Native: Alaska Indian      A      American Indian or Alaska Native: Alaska Indian
ebc459c9-b431-4385-b123-2f239dfd02cd      Native Hawaiian or Other Pacific Islander: Native Hawaiian      A      Native Hawaiian or Other Pacific Islander: Native Hawaiian
ebc459c9-b431-4385-b123-2f239dfd02cd      Black or African American: Other      A      Black or African American: Other
ebc459c9-b431-4385-b123-2f239dfd02cd      White: Other      A      White: Other
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Chinese      A      Asian: Chinese
ebc459c9-b431-4385-b123-2f239dfd02cd      American Indian or Alaska Native: Not Specified/Unknown      A      American Indian or Alaska Native: Not Specified/Unknown
ebc459c9-b431-4385-b123-2f239dfd02cd      Black or African American: African American      A      Black or African American: African American
ebc459c9-b431-4385-b123-2f239dfd02cd      Black or African American: African (Continental)      A      Black or African American: African (Continental)
ebc459c9-b431-4385-b123-2f239dfd02cd      Black or African American: Haitian      A      Black or African American: Haitian
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Korean      A      Asian: Korean
ebc459c9-b431-4385-b123-2f239dfd02cd      White: Arab or Middle Eastern      A      White: Arab or Middle Eastern
ebc459c9-b431-4385-b123-2f239dfd02cd      Hispanic/Latino: Puerto Rican (Island)      A      Hispanic/Latino: Puerto Rican (Island)
ebc459c9-b431-4385-b123-2f239dfd02cd      American Indian or Alaska Native: American Indian      A      American Indian or Alaska Native: American Indian
ebc459c9-b431-4385-b123-2f239dfd02cd      Asian: Filipino      A      Asian: Filipino
ebc459c9-b431-4385-b123-2f239dfd02cd      Hispanic/Latino: Mexican      A      Hispanic/Latino: Mexican
NULL      NULL      NULL      NULL
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38314154
>Why do I need the WHERE?
you don't need the WHERE ... it was just to show that the CASE statement is working correctly, visibly.

so, unless you are "hiding" something, the CASE construct will show the 'A' resp the description field for those records which have that GUID

to prove the other part: if you changed the WHERE clause to
WHERE ( EnumerationTypeId <> 'ebc459c9-b431-4385-b123-2f239dfd02cd' OR EnumerationTypeId IS NULL )

if will only show 'B's and NULLs indeed.
0
 
LVL 1

Author Comment

by:LCNW
ID: 38314157
Is there a datatype issue somewhere?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38314163
not that I can see.
so far, all is working fine
can you explain what the CASE statement should do, in your opinion?
I think you try to use it incorrectly in regards what you are trying to achieve.
0
 
LVL 1

Author Comment

by:LCNW
ID: 38314179
I have a table named EnumerationValue. It is a dumping ground for various descriptions of Users. I want to list the users and certain enumeration values. I'm provided the GUIDs as a way to get the correct info

So my result would be:

USERID                   Race                   Age                Weight
--------------------------------------------------------------------------------------------------------------
00001                     Asian                  23                  145

But those values would be linked to the GUIDs.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38314192
how are those "guid" links working?
from you last data sample, I don't see what you are trying to achieve. ...
0
 
LVL 1

Author Comment

by:LCNW
ID: 38314198
A guid for the race a guid for the age a guid for the weight.
0
 
LVL 1

Author Comment

by:LCNW
ID: 38314205
This is the best I can explain. One User ID multiple enumeration values I want to list out as columns.

Column           Column
USERID            EnumerationValue
----------------------------------------------------------------
Geoff               White (guid)
                        23 (guid)
                        180 (guid)

AS

Geoff           White             23              180
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38314223
ok, I see now.
you want to "group by" to get all the "values" into 1 row:

SELECT Abbreviation, Value
, MAX(CASE WHEN EnumerationTypeId = 'ebc459c9-b431-4385-b123-2f239dfd02cd' THEN Description   END ) AS Details
, MAX(CASE WHEN EnumerationTypeId = 'othergui' THEN Description  END ) AS OtherDetails
FROM        REPLICATION.dbo.EnumerationValue
GROUP BY Abbreviation, Value

Open in new window

0
 
LVL 1

Author Closing Comment

by:LCNW
ID: 38314263
That was it. I didn't know I needed to group by.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

850 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