The NVL function
You can use the NVL function to convert
an expression that evaluates to NULL to a value that you specify.
The NVL function accepts two arguments: the first
argument takes the name of the expression to be evaluated; the second
argument specifies the value that the function returns when the first
argument evaluates to NULL. If the first argument does not evaluate
to NULL, the function returns the value of the first argument. Suppose
a student table exists that includes name and address columns.
Suppose also that execution of the following query on the student table
returns the rows that the result shows.
The following query includes the NVL function,
which returns a new value for each row in the table where the address column
contains a NULL value.
You can specify any data type for the arguments of the NVL function provided that the two arguments evaluate to a common compatible data type.
If both arguments of the NVL function evaluate to NULL, the function returns NULL.
HCL OneDB™ also supports the NULLIF function, which resembles the NVL function, but has different semantics. NULLIF returns NULL if its two arguments are equal, or returns its first argument if its arguments are not equal. For more information about the NULLIF function, see the HCL OneDB Guide to SQL: Syntax.