Why do I get syntax error Msg 156 when using CASE expression?

Question

I am trying to run this simple query however it doesn't even parse saying

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

Query is:

select  
    case DMProject.dbo.TypeFourTraining.Column2 
    when Column2 between 181 and 360 then '181-360' 
    when Column2 between 0 and 180 then '0-180' 
END as score 
from DMProject.dbo.TypeFourTraining 

The same doesn't work for Column2 < 360 syntax neither..

I have searched over internet from msdn, and some other sites but I see that my syntax seems to be valid, then either there is a detail I need to know or there is something I can't see :(

Can anyone please suggest a solution?

Answer 1

You cannot mix the simple and searched types of CASE expression. Remove the field name DMProject.dbo.TypeFourTraining.Column2 specified after the CASE.

Correct syntax for your query:

SELECT  CASE 
            WHEN Column2 between 181    AND 360 THEN '181-360' 
            WHEN Column2 between 0      AND 180 THEN '0-180' 
        END as score 
FROM    DMProject.dbo.TypeFourTraining 

Two types of CASE expressions:

There are two types of CASE expression namely Simple and Searched. You cannot combine Simple and Searched in the same expression.

Simple CASE:

CASE input
    WHEN 1 THEN 'a'
    WHEN 2 THEN 'b'
    WHEN 3 THEN 'c'
    ELSE ''
END

Searched CASE with simple example:

CASE 
    WHEN input = 1 THEN 'a'
    WHEN input = 2 THEN 'b'
    WHEN input = 3 THEN 'c'
    ELSE ''
END

Searched CASE with slightly complex example:

This involves multiple columns. You can add multiple columns in each of the WHEN statements.

CASE 
    WHEN input = 1 AND second_column = 2 THEN 'a'
    WHEN input = 2 AND third_column  = 3 THEN 'b'
    WHEN input = 3 AND (second_column = 4 OR third_column = 6) THEN 'c'
    ELSE ''
END

Answer 2

You are mixing up the two forms of the case statement. Try this:

select (case when Column2 between 181 and 360 then '181-360' 
             when Column2 between 0 and 180 then '0-180' 
        END) as score 
from DMProject.dbo.TypeFourTraining 

The other form would be used for singleton values:

select  case DMProject.dbo.TypeFourTraining.Column2 
            when 1 then '1' 
            when 2 then '2'
            etc. etc. etc. 
            END as score 
from DMProject.dbo.TypeFourTraining