Use the IN operator within a case statement 2

[Mack2]

How do you use the IN operator within a case statement?
For example I need to do the following

Category =
CASE J.JobCodeDMID
WHEN IN(77,98,965) THEN 'Administration'
WHEN IN (62,102,654) THEN 'Teachers'
WHEN IN (73,34,693) THEN 'Support Staff'
END

It seems like I can not use the IN operator in the case statement. Is this true?

Thanks!!!!!

 

[markros]

I think you may want to use the second version of CASE function, e.g.
CASE WHEN J.JobCodeDMID IN (77,98, 965) then 'Administration'
CASE WHEN J.JobCodeDMID IN (62,102,654) then 'Teachers' etc. END

 

[RiverGuy]

Category =
CASE 
    WHEN J.JobCodeDMID IN(77,98,965)  THEN 'Administration'
    WHEN J.JobCodeDMID IN (62,102,654) THEN 'Teachers'
    WHEN J.JobCodeDMID IN (73,34,693) THEN 'Support Staff'
END

 

[Mack2]

Thanks Markos, I will run with that type. How does that affect performance.
Riverguy....I tried that, and an error came up pointing to the IN operator

Thank you for your help!!!!!!!!!!!!!!!!!!!!11

 

[markros]

I think there is missing space between IN and ( in the first statement, but I doubt it causes the error.

Also I prefer to use slightly different syntax, e.g.

CASE WHEN something then Something WHEN Something Else THEN Something Else END as Category

Though both should work the same.

 

[Mack2]

RiverGuy,

The only thing with doing the cases statement that way, is it creates different fields. I need all of the case statement for one field.

Looking at my case statement I need all the values to be under the category field.

Markos, I will give your example a try...

thanks!!!!!!

 

[Mack2]

Riverguy,

Sorry I got the names messed up. I tried your code example, it was perfect.

Thank you!!!!