Case When Between statement

[Zonie32]

Hi all,

Can someone tell me what I am doing wrong with this Case statement? If I comment out the "between" lines the query works fine. when i put them back in I get the error message 'Incorrect syntax near between'.

CASE procedure_code
when between '27301' and '27599' then 'KNEE INJ'
when between '29866' and '29889' then 'KNEE INJ'
when between '29850' and '29851' then 'KNEE INJ'
when between '73721' and '73723' then 'KNEE INJ'
when between '73560' and '73565' then 'KNEE INJ'
when '20610' then 'KNEE INJ'
when '29530' then 'KNEE INJ'
ELSE 'Nothing'
end as category

 

[r937]

you can use CASE expression WHEN syntax only if the operator is equality

otherwise, you must use the CASE WHEN expression syntax

CASE WHEN procedure_code BETWEEN '27301' AND '27599'
       OR procedure_code BETWEEN '29866' AND '29889'
       OR procedure_code BETWEEN '29850' AND '29851'
       OR procedure_code BETWEEN '73721' AND '73723'
       OR procedure_code BETWEEN '73560' AND '73565'
       OR procedure_code = '20610'
       OR procedure_code = '29530'
     THEN 'KNEE INJ'
     ELSE 'Nothing'
 END AS category

r937.com | rudy.ca