Reference Guide > TDV Support for SQL Functions > Conditional Functions > NVL2
 
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