vendredi 1 mai 2020

Add logic to else in case statement to handle nulls

Hello I have created a query that uses a case statement with substring on a column of data in sql to return values based on what's returned. if none of the data from the substring matches what I put in then I use else to just display the data from another column. this if fine unless it ends up being null. Is there a way I can use in if statement or something inside the else to handle nulls? example

select 
case
SUBSTRING(Name,3,3)
when 'USA' Then 'United States'
when 'CAN' Then 'Canada'
when 'AUS' Then 'Australia'
when 'BGT' Then 'Bogata'
else SiteName
end as 'Location Name'

from V_DevLocationData 

and I was trying to find a way to handle nulls in "SiteName" if case statement doesn't find any matches. I have tried

else (IF SiteName is Null THEN RETURN substring(Name0,3,3) ELSE RETURN SiteName END)

Cant get it work though

Aucun commentaire:

Enregistrer un commentaire