CODEWITHSUNDEEP
if-statementplsqlnestedcase
ninelondon
ninelondon

Reputation: 149

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.

Upvotes: 0

Views: 1473

Answers (1)

Belayer
Belayer

Reputation: 14886

The CASE structure allows nesting. So replace the IF...ELSE with outer case:

case when date < date '2020-01-01' 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
     end

     when date >= date '2020-01-01' and date < date '2020-01-07'
     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 
    
    else ...  ;

CAUTION: You should not use date as a column/variable name. While it is not a reserved word it is a data type definition. Using data type definitions as column/variable name is poor practice. Additionally I converted your implicit date strings to explicit ISO Standard. The ISO Standard is not required, but you should not relay on implicit data conversions.

Upvotes: 1

Related Questions