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 Smile. Just kidding Open-mouthed smile

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]

COALESCE

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 !!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s