Using Case When Then in SQL Server 2005 Express

• • Reply

  13 years ago

  by ErwinB

  Erwin Bergsma

  Leuven, Belgium

  Joined 15 years ago

  Hi,

  I'm trying to use a select-statement like this:

  SELECT ProductID, Sales, Score =
     CASE Sales
        WHEN Sales < 10 THEN 'Small'
        WHEN Sales < 100 THEN 'Medium'
        ELSE 'Big'
     END
  FROM Performance

  Which is the correct syntax if I have to believe various sites on SQL.  However, I get the following error:

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

  Does anyone have an idea why this is happening, and what I can do about it ?

  Thanks in advance,

  Erwin

  Report abuse
• • Reply

  13 years ago

  by James Crowley

  James Crowley

  London, United Kingdom

  Joined 20 years ago

  This isn't a SQL Express issue - as you get the same error against SQL Server 2000.

  Your problem is the reference to Sales after the CASE expression, when you're also comparing Sales in the WHEN Clause. You want

  SELECT ProductID, Sales, Score =
     CASE /* no sales here */
        WHEN Sales < 10 THEN 'Small'
        WHEN Sales < 100 THEN 'Medium'
        ELSE 'Big'
     END
  FROM Performance

  Report abuse
• • Reply

  13 years ago

  by ErwinB

  Erwin Bergsma

  Leuven, Belgium

  Joined 15 years ago

  That simple !

  Many thanks for the quick reply and solution.  I was starting to go crazy.  At least 5 articles on various websites used the syntax in my post...

  Report abuse
• • Reply

  13 years ago

  by Jack V

  Jack Vamvas

  , United Kingdom

  Joined 13 years ago

  The difference was that the way you were using it ,was a Simple case expression , whereas the solution (based on the Boolean logic you are using ) was a Searched Case Function.

  Both methods are valid , but used for different purposes

  Report abuse