Nested CASE - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Nested CASE

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-03-05 : 11:59:17

I am trying to to an update styatement using a nested case. I need soem help with the syntax....

CASE
WHEN rtrim(SS_Activ)= '16' and
case
when (rtrim([Function])='19') then 'PC' else 'EC'
end
END

Thanks

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-05 : 12:05:07

Not nested:
CASE WHEN rtrim(SS_Activ)= '16' AND (rtrim([Function])='19') THEN 'PC' ELSE 'EC'END

ChrisFretwell
Starting Member

43 Posts

Posted - 2004-03-05 : 13:46:31

Or did you want this
CASE WHEN rtrim(SS_Activ)= '16' AND (rtrim([Function])='19') then 'PC'
WHEN rtrim(SS_Activ)= '16' AND (rtrim([Function])<>'19') then 'EC'
end
with no update when the ss_active <> '16'?
Either way, no need to nest the case statement.

Chris

mdhingra01
Posting Yak Master

179 Posts

Posted - 2004-03-05 : 14:55:16

Thanks gus for the feedback, but the Case statement is not the complete statement, just a part of the statement. I just wanted to know if it is possible to nest Case in one other? IF so, what is the syntax like.

This is the complete statement:
UPDATE [WorkApp].[dbo].[colltmp]
SET program =
CASE
WHEN rtrim(activity)+rtrim(sub_activ)+rtrim(ss_activ)+rtrim([function])in('113000')THEN 'IM'

WHEN rtrim(activity)+rtrim(sub_activ)+rtrim(ss_activ)+rtrim([function])in('112800','112801','112802', '112803', '112804')THEN 'IB'

WHEN rtrim(activity)+rtrim(sub_activ)+rtrim(ss_activ)+rtrim([function])in('110722','110900','111310','111311','111320','147604','147605','142300','142301','142303','142304','142305','142306','142307','142308','142309','146110','211310','211320','211616','212309','410925','410936','410940','410945','410950','410960') THEN 'TO'

WHEN rtrim(activity)+rtrim(sub_activ)+rtrim(ss_activ)+rtrim([function])in('147100','147121','146100','146105','147410','147326', '147323') or substring(rtrim(activity)+rtrim(sub_activ)+rtrim(ss_activ)+rtrim([function]), 1, 4)in ('1472','1473') THEN 'PP'

WHEN rtrim(activity)+rtrim(sub_activ)+rtrim(ss_activ)+rtrim([function])in('111800','110780','111900') THEN 'CF'

WHEN rtrim(activity)+rtrim(sub_activ)+rtrim(ss_activ)+rtrim([function])in('210600','220600') THEN 'PD'

WHEN rtrim(activity)+rtrim(sub_activ)+rtrim(ss_activ)+rtrim([function])in('120100') THEN 'FN'

WHEN substring(rtrim(activity)+rtrim(sub_activ)+rtrim(ss_activ)+rtrim([function]), 1, 4)in('2102','2202') THEN 'TT'

WHEN rtrim(activity)+rtrim(sub_activ)+rtrim(ss_activ)+rtrim([function])in('110380') THEN 'NP'

WHEN substring(rtrim(activity)+rtrim(sub_activ)+rtrim(ss_activ)+rtrim([function]), 1, 1) = '3' THEN 'NS'

WHEN rtrim(activity)+rtrim(sub_activ)+rtrim(ss_activ)+rtrim([function])in('111115') THEN 'DA'

WHEN rtrim(activity)+rtrim(sub_activ)+rtrim(ss_activ)+rtrim([function])in('110100','110200','110300','110370','110371','110700','110701','110705','110707','110710','110711','110720','110740','110747','110810')THEN 'FE'

WHEN (rtrim(SS_Activ)= '05') or
(rtrim(SS_Activ)= '07' and rtrim([Function]) in ('45','80')) or
(rtrim(SS_Activ)= '13' and rtrim([Function]) in ('00','01','02','03','04','05','07','08','09','12','13','30','41'))or
(rtrim(SS_Activ)= '33' and rtrim([Function]) in ('00'))THEN 'CI'

WHEN (rtrim(SS_Activ)= '11' and rtrim([Function]) in ('12'))or
(rtrim(SS_Activ)= '12' and rtrim([Function]) in ('01'))or
(rtrim(SS_Activ)= '17') or
(rtrim(SS_Activ)= '16' and rtrim([Function])in ('19'))THEN 'PC'

WHEN (rtrim(SS_Activ)= '11' and rtrim([Function])<>'12') or
(rtrim(SS_Activ)= '13' and rtrim([Function]) in ('50','60'))THEN 'DE'

WHEN (rtrim(SS_Activ)= '12' and rtrim([Function]) <> '01') THEN 'CE'

WHEN (rtrim(SS_Activ)= '15') THEN 'IP'
WHEN (rtrim(SS_Activ)= '16' and rtrim([Function]) <> '19') THEN 'EC'

ELSE 'OT'
END

ChrisFretwell
Starting Member

43 Posts

Posted - 2004-03-05 : 16:32:42

Ah. Yes, nested case statements are possible.
For your first (simplified) one above the syntax you need to change the 'and' to a 'then'. so like this

case --- outer statement
when value1 = a then
case ---inner case statement
when value2 = x then 1
else 2 --inner case statement
end
when value1 = b then 3
else 4 --outer case statement
end

Make sense?

CASE
WHEN rtrim(SS_Activ)= '16' and
case
when (rtrim([Function])='19') then 'PC' else 'EC'
end
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources