Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Case Statements

Status
Not open for further replies.

luvmy2Js

IS-IT--Management
Sep 1, 2010
43
US
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!!

 
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
 
You simply forgot to close the inner CASE statement, e.g.
Code:
   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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top