Skip to content
Advertisement

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?

i am using sql 2012.

Advertisement

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.

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.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement