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)