CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
jrj
jrj

Reputation: 25

Case statement help for replacing null from multiple joins

so i have the following case below where if jl name does not have n/a i want it to come from the same column. if there is n/a i want to look into this subquery and find the recent jl name. but if the crecent.jl name is null i want to pull it from this other left join subquery that pulls the last record available from temprosRecent.[JL name]. i found the record in the temprosRecent.[JL name] when i test it separately but in my main query case statement below, i still get null for the record. is it due to the order of my case? i tried switching the order multiple times and didnt get it to work?

> ,CASE 
>           
>            WHEN EF.[JL name]!='N/A' then EF.[JL name]
>            
>            WHEN EF.[JL name]='N/A' THEN cRecent.[Jl_NAME]
>            
>            when cRecent.[Jl_NAME] IS null then temprosRecent.[JL name]
>           end as [NCW-Jl combo]

i am using sql 2012.

Upvotes: 0

Views: 113

Answers (2)

HABO
HABO

Reputation: 15816

The following code handles the 'N/A' cases and then defaults to returning temprosRecent.[JL name] when EF.[JL name] is NULL, the only remaining possibility. Note that it may still return null if temprosRecent.[JL name] is null.

Using coalesce handles the possibility of cRecent.[Jl_NAME] being null and returning temprosRecent.[JL name] instead.

CASE
  WHEN EF.[JL name] != 'N/A' then EF.[JL name]
  WHEN EF.[JL name] = 'N/A' THEN Coalesce( cRecent.[Jl_NAME], temprosRecent.[JL name] )
  ELSE temprosRecent.[JL name]
  end as [NCW-Jl combo]

Yes, the order is important. The result will be from the first matching condition. Note that short-circuiting is not guaranteed. If an expression has a side effect or might generate an error, e.g. divide-by-zero, that could occur even though the case is listed after the matching case.

Upvotes: 2

Popeye
Popeye

Reputation: 35900

You should use extra condition in when clause and re-order it like this:

 ,CASE 
    WHEN EF.[JL name]!='N/A' then EF.[JL name]
    when cRecent.[Jl_NAME] IS null and EF.[JL name]='N/A' then temprosRecent.[JL name]
    WHEN EF.[JL name]='N/A' THEN cRecent.[Jl_NAME]
  end as [NCW-Jl combo]

Upvotes: 1

Related Questions