Nested Case Statement - Pulling the wrong value

Question

Here is my syntax/question

,CASE   WHEN nullif(ltrim(A),'') IS NOT NULL OR nullif(ltrim(B),'') IS NOT NULL THEN NULL                                                                                       
        WHEN nullif(ltrim(C),'') IS NOT NULL        THEN (
                                                        CASE    WHEN nullif(MM,'') IS NOT NULL THEN MM
                                                                WHEN NN IS NOT NULL THEN XX                                                     
                                                                WHEN NN IS NULL THEN concat(UPPER(YY), ' ', UPPER(XX))      
                                                                END
                                                        )
        WHEN nullif(ltrim(D),'') IS NOT NULL OR nullif(ltrim(E),'') IS NOT NULL THEN concat(UPPER(XX), ' ', UPPER(YY))          
        ELSE ' '
        END as 'Data_Item'

We have a series of statements to evaluate. If fields A OR B are not null then we pull NULL

if field C is not null then we evaluate a nested case statement and this is where something is off with my code.

if field C is not null we should evaluate field MM if MM is not null we should return MM (this is what i should be getting but i'm not).

we continue to evaluate, if NN is not null we pull XX

if NN is null we concatenate YY space XX

and lastly if field D is not null or field E is not null then we concatenate fields XX space YY.

else we simply return a space then end

--

In short, its a semi-complex series case statement, if the second scenario is true we have to evaluate like 3 or 4 nested statements. Till we find what is true.

for whatever reason my data is always returning the last statement (D is true) rather than the nested one.

when nullif(ltrim(A),'') IS NOT NULL can be simplified to when A <> '' as empty strings and spaces compare equal. — dnoeth

dnoeth dnoeth · Accepted Answer · 2020-06-10T21:55:58

This is your CASE simplified:

CASE   
   WHEN A <> '' OR B <> ''
      THEN NULL                                                                                    
   WHEN C <> ''
      THEN CASE    
              WHEN MM <> '' THEN MM
              WHEN NN IS NOT NULL THEN XX                                                     
              WHEN NN IS NULL THEN Concat(Upper(YY), ' ', Upper(XX))     
           END
   WHEN D <> '' OR E <> ''
      THEN Concat(Upper(XX), ' ', Upper(YY))          
   ELSE ' '
END

Seems to match your logic ...

Nested Case Statement - Pulling the wrong value

1 Answers