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.

Answer

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.