asked on

SQL CASE

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.

Guy Hengel [angelIII / a3]

can you show the result of the EnumerationTypeid value from that query?
also, what is the data type of the column?

LCNW

ASKER

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

Guy Hengel [angelIII / a3]

well, then your CASE statement will also work ...
unless you have a typo somewhere or the like.

LCNW

ASKER

It's returning nulls

Guy Hengel [angelIII / a3]

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

LCNW

ASKER

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

Guy Hengel [angelIII / a3]

>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.

LCNW

ASKER

Is there a datatype issue somewhere?

Guy Hengel [angelIII / a3]

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.

LCNW

ASKER

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.

Guy Hengel [angelIII / a3]

how are those "guid" links working?
from you last data sample, I don't see what you are trying to achieve. ...

LCNW

ASKER

A guid for the race a guid for the age a guid for the weight.

LCNW

ASKER

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LCNW

ASKER

That was it. I didn't know I needed to group by.