Using Like in Case Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using Like in Case Statement

Author

Topic

mikecro
Starting Member

21 Posts

Posted - 2010-06-07 : 10:36:31

I am getting the following error: "Incorrect syntax near the keyword 'like'."

This looks fairly simple, but I can't find the problem. Thanks for any help.

Here's the code:

select d.department,
e.elective,
AIindicator = CASE e.elective
WHEN e.elective like '%AI%' then 'AI'
WHEN e.elective like '%Acting%' then 'AI'
ELSE 'NONAI'
END,
count(a.assignment_id) as studentCT,
count(b.num_slots) as offered,
round(cast(count(a.assignment_id) as float)/count(b.num_slots)*100.0,2) as perc

from.....

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-07 : 10:50:59

[code]
AIIndicator = CASE
WHEN e.elective like '%AI%' or WHEN e.elective like '%Acting%' THEN 'AI'
ELSE 'NONAI'
END,
[/code]

Jim

Everyday I learn something that somebody else already knew

mikecro
Starting Member

21 Posts

Posted - 2010-06-08 : 09:17:44

Thanks Jim, but that still generates the same syntax error.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 09:24:13

[code]AIIndicator = CASE
WHEN e.elective like '%AI%' or e.elective like '%Acting%' THEN 'AI'
ELSE 'NONAI'
END,
[/code]

Madhivanan

Failing to plan is Planning to fail

mikecro
Starting Member

21 Posts

Posted - 2010-06-08 : 09:41:55

Got it. I had to remove the variable name after CASE. Thanks for your help, guys!

working query:

AIindicator = CASE
when e.elective like '%AI%' then 'AI'
when e.elective like '%Acting Internship%' then 'AI'
ELSE 'NONAI'
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources