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,

​x
 
      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')

 
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