Link to home
Start Free TrialLog in
Avatar of Zack
ZackFlag for Australia

asked on

SQL Server CASE .. WHEN .. IN statement - Syntax issue

Heyas,

When I run the following SQL query

select case CurrentRating when IN ('-1', '[17-30]', '[30-45]') then 'Yes' else 'No' END AS YesNostatus,
count(*) from vwReg_Incidents_Posted
where ID in (select ID from IncidentClassificationsClaims where IncidentClass = 'Fall') 
Group by CurrentRating

Open in new window


I get the following result:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IN'.

What I am doing wrong here. The 'case' clause looks like it should be where it needs to be.

Any assistance is welcome.

Thank you.
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,
Pls try this..

SELECT 
YesNostatus , COUNT(*) Cnt
FROM 
(
	select case CurrentRating when IN ('-1', '[17-30]', '[30-45]') then 'Yes' else 'No' END AS YesNostatus
	from vwReg_Incidents_Posted
	where ID in (select ID from IncidentClassificationsClaims where IncidentClass = 'Fall') 
)K Group by YesNostatus

Open in new window


Hope it helps!
In the case expression, the column name is between the when and the in.
Also, use the case expression in your group by
SELECT
  CASE
    WHEN CurrentRating IN ('-1', '[17-30]', '[30-45]') THEN 'Yes'
    ELSE 'No'
  END AS YesNostatus,
  COUNT(*)
FROM vwReg_Incidents_Posted
WHERE ID IN (SELECT ID
            FROM IncidentClassificationsClaims
            WHERE IncidentClass = 'Fall')
GROUP BY 
  CASE
    WHEN CurrentRating IN ('-1', '[17-30]', '[30-45]') THEN 'Yes'
    ELSE 'No'
  END

Open in new window

Avatar of Zack

ASKER

Cheers for the syntax correction :)