SQLTeam.com | Weblogs | Forums

Incorrect Syntax using simple case

sql2005

#1

Hi Everyone,
Below is my SQL for a simple CASE statement and getting incorrect syntax message . Can somebody please help mein correcting the SQL

select  Requestid,CSN1 = case CSN  

when CharIndex(',',CSN)-1 < 0   THEN CSN
when CharIndex(',',CSN)-1 > 0 THEN Left(CSN,CharIndex(',',CSN)-1)
END
from VWCSN

#2

Can you post the actual error?

My quick guess is you need an ELSE clause I your CASE.


#3

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '<'.


#4

Actually I think your mixing the formats of CASE.

If you do CASE column the WHEN needs to be values.

If you do just case then you can have a series of expressions that evaluate to boolean.


#5

Try this (notice no CSN after CASE):

select  Requestid,CSN1 = case 
    when CharIndex(',',CSN)-1 < 0   THEN CSN
    when CharIndex(',',CSN)-1 > 0 THEN Left(CSN,CharIndex(',',CSN)-1)
END
from VWCSN

Then as suggested by @graz add in an ELSE.


#6

Thank You Sir. It worked