When outer joins became available in DB2 version 4 many of us were introduced to the SQL function COALESCE. Prior to version 4 its name was VALUE, but COALESCE is the ANSI standard name. I have continued to use VALUE and what is it VALUE does? Well, if the first parameter to the VALUE function evaluates to NULL the value of the second parameter is returned. If the second value also evaluates to NULL the value of the third parameter is returned and so on. If all parameters evaluates to NULL the function returns NULL. In version 6 a function called IFNULL were introduced. It works exactly like VALUE, but only accepts two parameters.
I hope there is no doubt in your mind about how practical it is to let VALUE replace a NULL value with a more useful value. What is more valuable to know is when DB2 returns NULL. It happens in many other cases than outer joins. An example is that DB2 does not fail when performing af division by zero. Instead NULL is returned as the result.
Many SQL functions returns the NULL value instead of a negative SQL code. Especially it is nice to know that MIN, MAX, AVG and SUM returns NULL if they must calculate their result based on zero rows. In my opinion SUM and AVG should return 0 in this case, but you may compensate by using VALUE(xxx(summed_column), 0) where xxx must be replaced with either SUM or AVG.
Another trick is to use the VALUE function to perform conversions. Many installations used anything but DATE defined columns to store dates in in the early days of DB2. Most date functions in DB2 returns NULL if you feed them with an invalid date format (many formats are accepted as valid). By using VALUE(date-function(old-fashioned-date), 'YYYY-MM-DD') a date is always returned and you can choose the value of YYYY, MM and DD in those cases.
Even more brilliant is the use of VALUE in subselects. Imagine a history table with columns keyvalue and keydate and a lot of other columns. Your job is to find all current rows after a given statusdate. The simple solution is to use a WHERE keydate >= statusdate, but this predicate is not enough because the rows that were already active at the statusdate are not selected. So a subselect is needed:
This SQL statement extracts the rows that were active at the statusdate together with all the rows with a keydate higher than the statusdate. On the other hand we now miss all the rows having a keyvalue without any dates lower than the statusdate. Here VALUE can help us by changing the subselect to SELECT VALUE(MAX(keydate), '0001-01-01') and leaving the rest of the SQL statement unchanged. Now the missing rows are selected.
When you have had some practise with VALUE and get some experience of the many situations where DB2 returns NULL values you will realise the strength of the VALUE function. First step is to expand the use of VALUE to the predicates used in the WHERE part of SQL instead of limiting the use just to the SELECT list. Then it may be possible to solve complex problem with simple SQL statements.