Using CASE and IN together

[jwestmore]

Hi everyone,

I'm writing a short proc and am trying to make my code easier to read by using CASE and IN together:

CASE test
WHEN IN('a','b','c') THEN '123'
WHEN IN('d','e','f') THEN '456'
WHEN IN('g','h','i') THEN '789'
END

Anyone see anything wrong syntactically here? This code generates an "Incorrect syntax near the keyword 'IN'" error message.

Thanks!
Jon

 

[swampBoogie]

CASE 
WHEN test IN('a','b','c') THEN '123'
WHEN test IN('d','e','f') THEN '456'
WHEN test IN('g','h','i') THEN '789'
END

 

[SQLBill]

Jon,

Your format is correct only when using exact match.

CASE test
WHEN 'a' THEN '123'
WHEN 'b' THEN '123'
WHEN 'c' THEN '123'
WHEN 'd' THEN '456'
<etc>

When doing any other comparison, the CASE statement needs to be like swampBoogie's suggestion.

-SQLBill