Nested CASE Statement With ELSe (SQL Server)

Question

I have a CASE statement something like following

CASE 
       WHEN A IS NULL 
       THEN CASE
                 WHEN B IN ('C','D') THEN NULL
                 WHEN X NOT IN ('C','D') THEN Z
                 End
                 ELSE SOMETHING_ELSE -- Want to get here When 'A' IS NOT NULL
                 END AS 'Result'

I want to get to ELSE part when First CASE is not true i-e 'A' is not NULL. Can anyone suggest if i have wrongly nested them? not getting results right.

Many Thanks,

Answer 1

First, you don't need to nest case statements. Just use one case:

select (CASE WHEN A IS NULL AND B IN ('C', 'D') THEN NULL
             WHEN A IS NULL AND X NOT IN ('C','D') THEN Z
             WHEN A IS NOT NULL THEN SOMETHING_ELSE
        END) as Result

Note that when A IS NULL but the first two conditions are not met, then the return value will be NULL.

Because case statements are evaluated sequentially, this is simpler to write as:

select (CASE WHEN A IS NOT NULL THEN SOMETHING_ELSE
             WHEN B IN ('C', 'D') THEN NULL
             WHEN X NOT IN ('C', 'D') THEN Z
        END) as Result

The first condition captures when A is not NULL. Hence the second two are when A is NULL.

Answer 2

You can add the second When to your case where you can check for the second condition and set value otherwise set default value.

CASE 
    WHEN A IS NULL THEN CASE
                          WHEN B IN ('C','D') THEN NULL
                          WHEN X NOT IN ('C','D') THEN Z
                        End
    WHEN A IS NOT NULL THEN yourdesiredvalue
    ELSE default value
    END AS 'Result'