Why is my case statement not returning the value in my "else" clause?

Question

I have this section of code and my ELSE is not working. It should return 'UNKNOWN' but I am still seeing NULLs in my results. What is going on?

      case 
            when t2.NEURO_GRP_2 is not NULL then t2.NEURO_GRP_1 --ASSIGN GROUP 2 BASED ON PRIMARY DX CODE
            when t2.NEURO_GRP_2 is null AND t6.NEURO_GRP_2 IS NOT NULL then t6.NEURO_GRP_2 --ASSIGN GROUP 2 BASED ON CLINIC
            when t2.NEURO_GRP_2 is null AND t6.NEURO_GRP_2 IS NULL then t3.NEURO_GRP_3 --ASSIGN GROUP 2 BASED ON PROCEDURE GROUP
            ELSE 'UNKNOWN' 
            end AS NEURO_GRP_2,

Answer 1

Perhaps you want coalesce():

coalesce(t2.NEURO_GRP_2, t6.NEURO_GRP_2, t3.NEURO_GRP_3, 'UNKNOWN')

This returns the first non-NULL value. As formulated with a constant as the last argument, it can never return NULL.

Answer 2

It really looks like coalesce() does what you intend:

coalesce(t2.NEURO_GRP_2, t6.NEURO_GRP_2, t3.NEURO_GRP_3, 'UNKNOWN') S NEURO_GRP_2

The problem with your original code probably is that:

• there is a mismatch in the first condition (you check the nullity of one column, but return another one)

• the conditions are somehow redondant, and not properly cascaded

You might have been meaning:

case 
    when t2.NEURO_GRP_2 IS NOT NULL then t2.NEURO_GRP_2
    when t6.NEURO_GRP_2 IS NOT NULL then t6.NEURO_GRP_2
    when t3.NEURO_GRP_3 IS NOT NULL then t3.NEURO_GRP_3
    else 'UNKNOWN' 
end AS NEURO_GRP_2

... Which is exactly what coalesce() does.