Case When Between statement help

[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

 

[RiverGuy]

Use this instead:

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

 

[Zonie32]

Totally works! Thanks so much RiverGuy.

 

[RiverGuy]

No problem.

 

[r937]

i like my solution better --

http://www.tek-tips.com/viewthread.cfm?qid=1487325

so tell me, zonie, which is it? mysql or mssql?

r937.com | rudy.ca

 

[RiverGuy]

That's great. I never actually noticed that the value being set was the same in each condition. However, I do believe that when the first WHEN is evaluated to true, no further checking will be done in the CASE statement. I'm not sure if SQL Server will short circuit all of the OR clauses in your code. What do you think?

 

[r937]

i do not have proof, but i believe that when one condition in a series of ORed conditions is true, no further checking is done

r937.com | rudy.ca