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,
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
.
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With