jeudi 12 août 2021

PL SQL - Case When statement inside If statement?

I need to return a field "AMOUNT" which is calculated on 2 different columns.

Initially had this (I know I could have OR statements, but it's easier to read this way):

CASE WHEN [condition AND condition] THEN 1
WHEN [condition AND condition] THEN 1
WHEN [condition AND condition] THEN 1
WHEN [condition AND condition] THEN 2
WHEN [condition AND condition] THEN 2
WHEN [condition AND condition] THEN 2
WHEN [condition AND condition] THEN 3
WHEN [condition AND condition] THEN 3
WHEN [condition AND condition] THEN 3
ELSE 4
END AS AMOUNT

However, the conditions change based on the date column. So now I am looking to write a CASE WHEN STATMENT inside an IF-ELSEIF-ELSE statement

IF (DATE < 01.01.2020) THEN
CASE WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 3
WHEN [condition] THEN 3
WHEN [condition] THEN 3
ELSE 4

ELSEIF (DATE >= 01.01.2020 AND DATE <01.07.2020) THEN
CASE WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 3
WHEN [condition] THEN 3
WHEN [condition] THEN 3
ELSE 4

ELSE
CASE WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 1
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 2
WHEN [condition] THEN 3
WHEN [condition] THEN 3
WHEN [condition] THEN 3
ELSE 4

END AS AMOUNT

It does not seem to work so I think there might be a syntax problem.

Aucun commentaire:

Enregistrer un commentaire