VALUE (COALESCE ) function in DB2


In one of my Previous post , i discuss about a way to handle a null in your application program (by using Null indicator). However this post explain one more way to handle a null by using Scalar function.

As you know if my column is defined as NULL and i haven't use any null handling technique in my application program then i may get the error ( SQL code -305). To avoid this we can use null indicator (explained in detail here ).

You can get around this situation by using couple of Scalar function too,which can be part of your SQL query.

These Scalar function are VALUE and COALESCE. Both of these function can be used to supply a value whenever DB2 returns a NULL. Let's see how exactly these function works.

Example :

Let's see i want to select salary of an employees from an EMP table where salary is greater than 1000 and also salary column is defined as NULL.

Then i can use the Value function as below.


     SELECT * FROM EMP
         WHERE VALUE (SALARY,0)  > 1000;


Now this function VALUE (SALARY,0)  > 1000 will return the actual value of SALARY when the column is NOT NULL (means the employee have some value as a SALARY ) and return 0 when the SALARY for employee is NULL. You can put any value instead of 0 here and whenever the SALARY for particular employee is NULL it will be replaced by that default value you placed.The DATA Type of default value you are providing must match with the DATA Type of column (SALARY in this case).

Simply you can read it as ,

IF SALARY NOT NULL then SALARY else 0.

COALESCE function works the same way as VALUE and have the same syntax.

That is COALESCE (SALARY,0)

20 comments:

Anonymous said...

Thanks Nitin!! The post was really helpful!

Nitin Gandhi said...

Thanks..it means lot to me.

Anonymous said...

Nitin,

Hats off...! Superb explanation in lucid way.

Thanks
Ganesh K Mynampati

Anonymous said...

Is there a way to look for empty string instead of null ? Like functionX('', 'XYZ') whenever it sees an empty value, it should repalce it with 'ABC'. The requirement here is we store empty string if user does not select a value and in return we display a text whenever a column contains empty string. Now when you sort that column on UI side, it displays the replaced text 'XYZ' on top as on the background, it sees it as ''.

Nitin Gandhi said...

How about using CASE.

Case
when var1 = ' '
then 'ABC'
End

Anonymous said...

I do appreciate your effort.

Thanks,

and one day you will reward for this good deed.

Syed

Anonymous said...

VERY INTERESTING POST...
MUCH HELPFULL,,

Anonymous said...

What i don't realize is actually how you're not actually a
lot more smartly-favored than you might be right now. You're so intelligent. You understand therefore considerably with regards to this topic, produced me personally imagine it from so many numerous angles. Its like women and men are not involved until it is one thing to accomplish with Girl gaga! Your own stuffs nice. All the time take care of it up!

Feel free to visit my weblog; bancuri videoclipuri

Anonymous said...

each time i used to read smaller content that also clear their motive, and that is also happening with this post
which I am reading now.

Look into my weblog: diets that work

Anonymous said...

Very nice article, just what I wanted to find.

Here is my web site - parquet pas cher paris

Anonymous said...

Hi there colleagues, nice paragraph and nice urging commented at this place, I am truly enjoying by these.


Feel free to visit my webpage phenix option

Anonymous said...

Incredible points. Sound arguments. Keep up the good effort.



my blog post :: fenetre pvc sur mesure devis en ligne

Anonymous said...

Aѕking questіons arе reallу plеasant thing if you are not understanding anything fully, however this ρarаgгaph provides
nice understanding even.

Αlso vіsit my weblog :: parquet vitrifie

Anonymous said...

Hi there! Someone in my Facebook group shared this
site with us so I came to look it over. I'm definitely enjoying the information. I'm book-marking and will be tweeting this to my followers!

Terrific blog and great design and style.

my web-site installer une terrasse en bois

Anonymous said...

you're truly a excellent webmaster. The website loading pace is incredible. It seems that you're doing
any unique trick. Also, The contents are masterwork. you have performed a magnificent job on this matter!


Here is my blog :: comment gagner de l'argent

Anonymous said...

Admiring the time and effort you put into your
website and in depth information you provide.
It's awesome to come across a blog every once in a while that isn't the same outdated rehashed material.
Great read! I've bookmarked your site and I'm adding your RSS feeds to
my Google account.

Have a look at my web-site; renover un parquet sans poncer

Anonymous said...

After going over a number of the blog articles
on your web site, I seriously appreciate your way of writing a
blog. I bookmarked it to my bookmark site list and will be checking back in the near future.
Please check out my web site too and let me know how you feel.


My homepage; location housse de chaise

Srikar said...

You made it very easy to be understandable..
Thanks a lot Nithin..!

ADA said...

Thanks a lot sir...Its really helpful while preparing for an interview..

Anonymous said...

Thank you so much..its really helpful to understand about both the scalar functions.

Post a Comment