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

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

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.

Advertisement

Answer