CODEWITHSUNDEEP
sqlsql-serversyntaxsyntax-errorcase
salman khan
salman khan

Reputation: 25

How to fix this in case expression?

Select 
    ID, date_d,
    Case ID 
       when ID >= 0 then "greater" 
       else "Smaller"
    End as Grtr 
from 
    emp_date

Error:

Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '>'

Input

ID      date_d
----------------
1   2018-02-02
2   2018-03-31
3   2019-03-31

Upvotes: 2

Views: 50

Answers (2)

Mureinik
Mureinik

Reputation: 311723

The shorthand syntax (case expression when value ...) is basically syntactic sugar over a list of equal conditions. If you want to use other operators, you need to use the full syntax (case when condition then...). E.g.:

Select ID,date_d,
Case 
   when ID >= 0 then 'Greater'
    Else 'Smaller'
    End as Grtr 
from emp_date

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175874

You need to use searched case expression(original version is mixing both simple/searched syntax):

Select ID,date_d, Case  when ID >= 0 then 'greater' Else 'Smaller' End as Grtr 
from emp_date

CASE:

Searched CASE expression:  
CASE  
     WHEN Boolean_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END

Upvotes: 1

Related Questions