8.13.4 NVL

Evaluates the specified target data in the order target-data-1, target-data-2, and then returns the first non-null value.

Organization of this subsection

(1) Specification format

scalar-function-NVL ::= NVL(target-data-1,target-data-2)
 
  target-data-1 ::= value-expression
  target-data-2 ::= value-expression

target-data-1, target-data-2:

Specifies the target data.

The following rules apply:

Specify target-data-1 and target-data-2 in the form of value expressions. For details about value expressions, see 7.20 Value expression.
In target-data-1 and target-data-2, specify data whose data types can be compared. For details about data types that can be compared, see (1) Data types that can be compared in 6.2.2 Data types that can be converted, assigned, and compared.
If the data type of target-data-1 is DATE, TIME, or TIMESTAMP, you can specify a character string literal that adheres to the format of the predefined input representation for target-data-2. For details about the predefined input representations, see 6.3.3 Predefined character-string representations.
You cannot specify a dynamic parameter by itself for target-data-1.
If you specify a dynamic parameter for target-data-2, the data type of the dynamic parameter is assumed to be the data type of target-data-1.
You must specify a value for target-data-2 that is capable of being assigned to the data type of target-data-1. For details about storage assignments, see (2) Storage assignments between data types in 6.2.2 Data types that can be converted, assigned, and compared.

The data type and data length of the execution result will be the data type and data length of target-data-1.
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
If target-data-1 and target-data-2 have null values, the execution result will be a null value.
If target-data-1 has a null value, the value of target-data-2 is converted to the data type and data length of target-data-1.

Example:

Execute the scalar function NVL on the values in column C1 and column C2 in table T1.

SELECT NVL("C1","C2") FROM "T1"

[Figure]