%Coalesce Meta-SQL

If you're like me, you might first wonder what the word Coalesce even means? Well in terms of SQL constructs, it refers dealing with NULL values. If you're an Oracle user, you might be more familiar with the nvl operation.

A common use for coalesce/nvl is to substitute a default value when a NULL is encountered. Probably the most common example is to substitute a NULL with a 0 on fields you are using for calculations.

So for example:

select QTY * PRICE from YOUR_TABLE;

Works fine unless you have a scenario where either the quantity or price is NULL. Yes that may be weird but this is data we are talking about. Also think what would happen if you were doing a division operation instead of multiplication.

Here's how to write the same statement with coalese using Meta-SQL:

select %Coalesce(QTY, 0) * %Coalesce(PRICE, 0)
from YOUR_TABLE;

Simple, on resolving the Meta-SQL it will be turned into the appropriate syntax for your database platform. If you use Oracle you might wonder why you would bother when you can just use nvl? Well like a lot of other Meta-SQL functions, the benefit is database platform independence. Your code will work on any database supported by PeopleSoft. Up to you.