asked on

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 ...

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Patrick Matthews

SUM((CASE WHEN pyrlrtyp = 2 THEN (CASE WHEN payrolcd LIKE 'erm%' OR payrolcd LIKE 'fer%' THEN uprtrxam ELSE 0 END) ELSE 0 END))

SQL_SERVER_DBA

SUM(
CASE WHEN pyrlrtyp = 2 THEN
CASE payrolcd
WHEN LIKE 'erm%' THEN uprtxam
WHEN LIKE 'fer%' THEN uprtxam
END
ELSE 0 END)

SQL_SERVER_DBA

WHEN LIKE (valley girl)

Tunkster2

ASKER

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