Using Like within a case statement

I need to have part of a question not have Like and the other to have like - can't seem to get both to reside in the same statement...
In SQL 2000
It appears that the WHEN word needs to be AFTERthe field unless I use the LIKE then the WHEN  needs to be BEFORE the field ... as in:
Case payrolcd WHEN  'ermedp' then uprtrxam
vs.
Case WHEN payrolcd like 'erm%' then uprtrxam
I am trying to encompass both in the same statement w/ a sum - something like:
SUM(CASE pyrlrtyp WHEN 2 THEN
           case payrolcd when 'ermedp'  then uprtrxam
                                  when like 'fer%'  then uprtrxam
 ELSE 0 END ELSE 0 END)   ,


Any way to do this ...
Tunkster2Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SUM(CASE WHEN pyrlrtyp  = 2 THEN
           case when payrolcd  = 'ermedp'  then uprtrxam
                                  when payrolcd like 'fer%'  then uprtrxam
 ELSE 0 END ELSE 0 END)   ,
0
 
Patrick MatthewsCommented:
SUM((CASE WHEN pyrlrtyp = 2 THEN (CASE WHEN payrolcd LIKE 'erm%' OR payrolcd LIKE 'fer%' THEN uprtrxam ELSE 0 END) ELSE 0 END))
0
 
SQL_SERVER_DBACommented:
SUM(
CASE WHEN pyrlrtyp = 2 THEN
     CASE payrolcd
        WHEN LIKE 'erm%' THEN uprtxam
        WHEN LIKE 'fer%'   THEN uprtxam
     END
ELSE 0 END)
0
 
SQL_SERVER_DBACommented:
WHEN LIKE (valley girl)
0
 
Tunkster2Author Commented:
Appreciate it - I didn't realize that in switching the WHEN to the back from the front I needed to add the " =  " sign... duh - your answer did not highlight that - so it took me a bit to apply correctly...
Thanks for the quick response !!!
I'll have another in a minute
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.