Help on CASE statment - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help on CASE statment

Author

Topic

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-10-23 : 13:11:44

How can I use case statement on more than one fields ?

If an employee has selected just one Ethnic code then
Ethnicity = Code1
if 'Hispanic' Indicator selected along with just one Ethnic code then '_HIS'
If an employee has selected more than one
Ethnicity = '_Two'

I have following TSQL commented part is not working
ETHNIC_CODE =
(case SUBSTRING(isnull(stu.Racex,''),3, 2) -- If second racex has nothing
when 0 then
--CASE HispanicLatino
-- WHEN 'Y'THEN '__HIS'
--END
CASE SUBSTRING(isnull(stu.Racex,''),1, 2)
WHEN 0 THEN ''
ELSE (SELECT CAST(StateCode AS VARCHAR) FROM dbo.tblVldEthnic WHERE SUBSTRING(Racex,1, 2) = ID)
END
else
'__TWO'
end)

Niki

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-23 : 13:21:23

I really don't understand the logic you want to apply. Can you provide an illustration with sample data and expected output? Here are some links that might help you prepare that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-10-23 : 13:24:12

I think you need to supply the minimum table definition, sample data and expected results. I can't follow what you want.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-10-23 : 13:33:38

My table has two columns 'Racex' and 'HispanicLatino'. RaceX is an array of 2-digit Ethnic codes. I need to convert it to output ETHNIC_CODE
Here is what I want to do -
If 'HispanicLatino' = 'Y' THEN
ETHNIC_CODE = '_HIS'
if Racex(3,2) = Null and 'HispanicLatino' = 'N' then
ETHNIC_CODE = Racex(1,2)
else
ETHNIC_CODE = '_TWO'

I hope it clarifies my question.

Niki

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-10-23 : 14:10:31

Made it to work like this -

ETHNIC_CODE =
CASE stu.HispanicLatino
WHEN 'Y' THEN '__HIS' -- if hispanic then '__HIS'
WHEN 'N' THEN
(case SUBSTRING(isnull(stu.Racex,''),3, 2)
when 0 then -- If second racex has nothing, then check the first one
CASE SUBSTRING(isnull(stu.Racex,''),1, 2)
WHEN 0 THEN ''
--if only one ethnic code selected
ELSE (SELECT CAST(StateCode AS VARCHAR) FROM dbo.tblVldEthnic WHERE SUBSTRING(Racex,1, 2) = ID)
END
else
'__TWO' --When multiple Ethnic codes selected
end)
END ,

Niki

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-23 : 18:31:30

there are two potential issues i find in this

1. (SELECT CAST(StateCode AS VARCHAR) FROM dbo.tblVldEthnic WHERE SUBSTRING(Racex,1, 2) = ID)

this will work only if it returns always single record for the ID. if by any chance above SELECT returns multiple records then it breaks

2. See part in blue above. You've not specified a length while casting to varchar which can cause below unexpected result

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-10-24 : 18:37:44

Thank you Visakh! Yes, I have only one records returned for each ID.

Niki

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-10-24 : 20:46:03

[code]
Source
If 'HispanicLatino' = 'Y' THEN
ETHNIC_CODE = '_HIS'
if Racex(3,2) = Null and 'HispanicLatino' = 'N' then
ETHNIC_CODE = Racex(1,2)
else
ETHNIC_CODE = '_TWO'[/code]

[code]
output
CASE WHEN Racex(3,2) IS Null and HispanicLatino = 'N' then
ETHNIC_CODE = Racex(1,2)
WHEN HispanicLatino = 'Y' THEN
ETHNIC_CODE = '_HIS'
ELSE
ETHNIC_CODE = '_TWO'
END
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 00:06:08

quote:

Originally posted by waterduck


Source
If 'HispanicLatino' = 'Y' THEN
	ETHNIC_CODE = '_HIS'
if Racex(3,2) = Null and 'HispanicLatino' = 'N' then
	ETHNIC_CODE = Racex(1,2)
else
	ETHNIC_CODE = '_TWO'


output
CASE WHEN Racex(3,2) IS Null and HispanicLatino = 'N' then
	ETHNIC_CODE = Racex(1,2)
WHEN HispanicLatino = 'Y' THEN
	ETHNIC_CODE = '_HIS'
ELSE
	ETHNIC_CODE = '_TWO'
END

this CASE wont work

it should be


ETHNIC_CODE =CASE WHEN Racex(3,2) IS Null and HispanicLatino = 'N' then
	 Racex(1,2)
WHEN HispanicLatino = 'Y' THEN
	 '_HIS'
ELSE
	 '_TWO'
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources