 |
| SQL COALESCE scalar function in Db2 for i SQL |
COALESCE
The COALESCE function returns the value of the first Non-Null expression. The result could be null only when all the arguments are null. Also, second argument must be compatible data type as per the first argument.
Syntax
COALESCE(expression1, expression2)
Example#1:
SELECT Coalesce('TEST', ' ') FROM SYSIBM.SYSDUMMY1
Here, both the arguments are not null therefore, first argument will get return.
COALESCE TEST
Example#2:
SELECT Coalesce(NULL, 'A') FROM SYSIBM.SYSDUMMY1
Here, first argument is NULL and the second argument is not null therefore, second argument will get return since that is the first non-null argument.
COALESCE A
Example#3:
Student table has below record where name is null
SELECT * FROM student WHERE name is NULL
ROLLNO NAME SUBJECTID
1 - 205
Let's use below query that will return NULL as a result.
SELECT coalesce(NULL, name) FROM student WHERE name is NULL
Here, first argument is NULL and the second argument will also return null therefore, NULL will be returned as no argument has Non-null value.
COALESCE -