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