Case Statements SQL | OutSystems

Edward S

Case Statements SQL

Question

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

21 Oct 2015

Kilian Hekhuis

MVP

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.

21 Oct 2015

Edward S

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]

22 Oct 2015

Kilian Hekhuis

MVP

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.

22 Oct 2015

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines