CODEWITHSUNDEEP
mysqlsql
Drew
Drew

Reputation: 1

Nested CASE statement with 1 or more WHEN clause

I am not sure but I am getting NULLS back??

I am trying to write a query to evaluate using 2 WHEN clauses for one CASE statement. Each WHEN clause has more nested WHEN clauses in it.

I am trying to say that if the first when clause is correct then check the nested when clause and etc. But if the first WHEN clause is not correct then go to the second WHEN clause.

SELECT
    ID,
  CASE 
    WHEN condition1 >= 4
        THEN
            CASE 
                WHEN condition2  = 'resul1'
                    THEN
                        CASE 
                            WHEN condition3 >= 1 
                                THEN 'Pre_Final'
                     END
            END
     WHEN condition1 >= 4
        THEN
            CASE 
                WHEN condition2  = 'result2'
                    THEN 'Pre_Final'
        END
  ELSE 'NOT Pre_Final'
    END AS 'Criteria_Type_1'
FROM table1

Upvotes: 0

Views: 544

Answers (1)

Joey Dubs
Joey Dubs

Reputation: 41

I am not able to test at the moment, but I believe you are looking to have multiple conditions on your case. You can chain your when conditions with an and instead of nesting. With nesting, once the outer case is matched it will not break out into a different case.

Reworking your example:

SELECT
    ID,
    CASE 
        WHEN condition1 >= 4 AND condition2  = 'resul1' AND condition3 >= 1 
            THEN 'Pre_Final'
        WHEN condition1 >= 4 AND condition2  = 'result2'
            THEN 'Pre_Final'
        ELSE 'NOT Pre_Final'
     END AS 'Criteria_Type_1'
FROM table1

Reference material: https://www.sqlshack.com/understanding-sql-server-case-statement/

Upvotes: 1

Related Questions