Case Statements

[luvmy2Js]

I have a case statment with similar criteria's in them:

Case when role = '8020' and id in (select id from exec) then 'Exec Bonus'
when role = '8020' and not id in (select id from exec) then 'Monetary'
Else '' end.

What is happening is the ones that are not in "exec" are coming back blank? It looks like only the first part of the case statement is being met and I am thinking it is because they are both looking at role =
'8020'. How can I get around this issue?

Thanks a million!!

 

[markros]

Try

case when Role = '8020' 
    then
       case when exists (select 1 from Exec where Exec.ID = myMainTable.ID) then 'Exec Bonus' else 'Monetary' end
ELSE '' end

PluralSight Learning Library

 

[luvmy2Js]

Okay I added all my criteria in and took your code above and added:

[One-Time Payment Plan] = Case when CONVERT(nvarchar, DecryptByKey(p15.[Wage Type])) = '4000' then 'Commission Payment'
when CONVERT(nvarchar, DecryptByKey(p15.[Wage Type])) = '3030' then 'Recognition Award (Monetary)'
when CONVERT(nvarchar, DecryptByKey(p15.[Wage Type])) = '3000' then 'P3 Bonus'
when CONVERT(nvarchar, DecryptByKey(p15.[Wage Type])) = '4020' then 'Commission Guarantee Payment'
when CONVERT(nvarchar, DecryptByKey(p15.[Wage Type])) = '4010' then 'P3 Bonus'
when CONVERT(nvarchar, DecryptByKey(p15.[Wage Type])) = '3010' then 'P3 Bonus'
when CONVERT(nvarchar, DecryptByKey(p15.[Wage Type])) = '3020'
then
case when exists (select 1 from cvt_ExecJobCodeAssignments where p15.sapid = empid)
then 'Other' else 'Recognition Award (Monetary)'
END,


Now I am getting error message:

Incorrect syntax near ',' and it takes me to the comma after the END in the Case Statement

 

[markros]

You simply forgot to close the inner CASE statement, e.g.

   case when exists (select 1 from cvt_ExecJobCodeAssignments where p15.sapid = empid) 
                                            then 'Other' else 'Recognition Award (Monetary)' end -- this end was missing
END

PluralSight Learning Library

 

[luvmy2Js]

Oh man! Thanks for that!!