NVL2
The NVL2 (Null Value Replacement 2) function lets you replace both non-NULL and NULL values in the returned result set.
Syntax
NVL2 (expression, value_if_NOT_NULL, value_if_NULL)
Remarks
• NVL2 tests the values returned by the column or variable defined by expression.
— If a value returned is not NULL, the function replaces that value with the second expression (value_if_NOT_NULL).
— If the value returned is NULL, the function replaces that value with the third expression (value_if_NULL).
• If a replacement value character string is not numeric or set off by single-quotes, it is interpreted as a column name. In this case, the result set is replaced with the value found in the column corresponding to the result of the NULL test.
• NVL2 treats empty strings as NULL.
• NVL2 follows the ANSI SQL rules for data type precedence.
Example (Testing for a Completion Value)
For the column named CompletionTime, a non-NULL value indicates that the transaction was completed, and so the return value is 1. If CompletionTime has a NULL value, the return value is 0.
NVL2 (CompletionTime, 1, 0) FROM Transaction_Table
Example (Checking a Timestamp)
In this example, SELECT NVL2 checks to see if a time stamp is set in the PymtPosted column. If it has a non-NULL value, the string “Yes” is returned in the result set. If the value of PymtPosted is NULL, the value from the corresponding row in the column named Acct_Status is returned in the result set.
SELECT NVL2 (PymtPosted_timestamp, 'Yes', Acct_Status) FROM table
Example (Checking for a Value or NULL)
In this example, an appropriate string is returned for each row in the named column, depending on its value.
SELECT NVL2 (ColName, 'This had a value.', 'This was NULL.') FROM table