Case Statements SQL

Case Statements SQL

  
Hello,

I have an entity called Year, with attributes M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12 and comment.
 There are numbers in each month.
I want to use a CASE Statement, for example

SELECT
    CASE {Year}.[M1]
     WHEN {Year}.[M1] > 2 THEN "Hooray"
     WHEN {Year}.[M1] <2 THEN "Boooo"
 ELSE "Fair Enough"

END AS {Year}.[Comment]
FROM {Year}
ORDER BY {Year}.[Comment]

It says there is a missing keyword. Essentially, I want a CASE statement that looks at the value of the month and dependent on what the value is, says one of three different things (Hooray, Boo, or Fair Enough). I am probably a long way of making this work. Does anyone have any ideas? Any help would be greatly appreciated.

Kind regards,

Edward
The problem is in your AS - you can't specify a table name there. Also, unless you use a query on a named sub query, you can't use AS attributes in the ORDER BY.
Hi Killian.

Thankyou. I changed the order by, and I used END AS Comment. Comment is the second column in the structure after M1. However, it says there is a missing keyword? Sorry, CASE statements I have always found tricky!

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 1573 StartFragment: 314 EndFragment: 1541 StartSelection: 314 EndSelection: 314
SELECT
    CASE {Year}.[M1]
     WHEN {Year}.[M1] > 2 THEN "Hooray"
     WHEN {Year}.[M1] <2 THEN "Boooo"
 ELSE "Fair Enough"

END AS Comment


FROM {Year}
ORDER BY {Year}.[M1]
Hi Edward,

There are two variants of the CASE statement, which you are mixing here (and that doesn't work :)). Variety 1:

CASE variable
WHEN value1 THEN xxx
WHEN value 2 THEN xxx
END

Variety 2:

CASE
WHEN condition1 THEN xxx
WHEN condition2 THEN xxx
END

So the solution to your problem is to remove "{Year}.[M1]" after the CASE, and it should run fine.