Find first not null value from different columns TIP # 47

 

Problem:

Sometimes, it might be possible that we need not null value only from particular columns and if all column have null value then we provide a default value.

Lets understand this by a general and very interesting example

suppose a friend come to your house and you want to give him a treat then you check your first column or we can say first option “Is there any thing to eat ?” if that value is null then you go for second column or we can say second option “ Is there any thing to drink ?”

If that value is also null then you will choose 3rd or default option and ask friend to give you treat.

Isn’t it simple . Just kidding

Let’s understand  now with adventurework’s product table.

Suppose, We want to fetch productId, productname,product number, and any property (either color, class) and if both the column (color, class) are null then need to display “No Property found” in the column value.

so  I wrote following query  to achieve this

SELECT PRODUCTID ,
      Name,
     ProductNumber,
COALESCE(Color,class,'No Property found') As productProperty
FROM [Production].[Product]

so if you observer above figure you will find in the records where color found color value appear and if color value is null and class value found the class value appear and if both color and class value is null then we provide simple value which is “No Property found”

I hope this may help you.

Thanks  & Enjoy

RJ !!