Help with Case statement please 2

[jpeters01]

Very much a Newb here, trying to create a case statement with multiple 'likes' and I do not know the correct syntax is or if this is even possible. This is just a sample, the actual case statement is bigger:

CASE
WHEN casepro.act_proname like ['%COLOS%','%COLON%'] then "COLO"
WHEN psmpro.pro_abbr like ['HIPTOTAL','HIPBI'] then "HPRO"
WHEN psmpro.pro_abbr like '%KNEETOTAL%' then "KPRO"
ELSE '###'
END as procCode,

Thank you for looking at this!

 

[gmmastros]

You cannot "double up" your like statements. Instead, try something like this...

CASE 
WHEN casepro.act_proname like '%COLOS%' then "COLO"
WHEN casepro.act_proname like '%COLON%' then "COLO"
WHEN psmpro.pro_abbr like 'HIPTOTAL' then "HPRO" 
WHEN psmpro.pro_abbr like 'HIPBI' then "HPRO" 
WHEN psmpro.pro_abbr like '%KNEETOTAL%' then "KPRO" 
ELSE '###'	
END as procCode,

This code should work.

In addition, I encourage you to always use single-quotes when using strings. Double-quotes will sometimes work, but may eventually cause you problems.

CASE 
WHEN casepro.act_proname like '%COLOS%' then 'COLO'
WHEN casepro.act_proname like '%COLON%' then 'COLO'
WHEN psmpro.pro_abbr like 'HIPTOTAL' then 'HPRO'
WHEN psmpro.pro_abbr like 'HIPBI' then 'HPRO'
WHEN psmpro.pro_abbr like '%KNEETOTAL%' then 'KPRO'
ELSE '###'	
END as procCode,

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dhookom]

I assume this will work:

WHEN psmpro.pro_abbr like 'HIPTOTAL' then 'HPRO'
WHEN psmpro.pro_abbr like 'HIPBI' then 'HPRO'

But, since there is no wildcard, isn't it more efficient to use:

WHEN psmpro.pro_abbr = 'HIPTOTAL' then 'HPRO'
WHEN psmpro.pro_abbr = 'HIPBI' then 'HPRO'

Duane
Hook'D on Access
MS Access MVP

 

[jpeters01]

I was thinking that might be the 'case' Thanks for the extra tip!

 

[DaveInIowa]

Just to be different, here's a couple more options although I can't vouch for the efficiency over the above options:

CASE 
   WHEN casepro.act_proname LIKE [red]'%COLO[SN]%'[/red] THEN 'COLO'
   WHEN psmpro.pro_abbr [red]IN ('HIPTOTAL','HIPBI')[/red] THEN 'HPRO' 
   WHEN psmpro.pro_abbr LIKE '%KNEETOTAL%' THEN 'KPRO' 
   ELSE '###' 
END AS procCode,