SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
9-118
NVL Function
NVL Function
The NVL function returns expr2 if expr1 is NULL. Otherwise, the function returns
expr1.
expr1 and expr2 specify value expressions and must be of comparable data types.
The NVL function is logically equivalent to the following IF-ELSE expression:
if (expr1 is NULL)
return expr2
else
return expr1
Considerations
•
If all the expressions in the argument list are fixed-length character types, the
return value is a fixed-length character string equal to the maximum size of all the
expression value types.
•
If any of the expressions in the argument list are variable-length character types,
the return value is a variable-length character string with maximum size of all the
expression value types.
•
If all the expressions in the argument list are integer types, the return value is of
the same data type as the largest integer type of all the possible return values.
•
If all the expressions in the argument list are numeric types, and at least one is
REAL, FLOAT, or DOUBLE PRECISION, the return value is a DOUBLE
PRECISION type.
•
If all the expressions in the argument list are numeric types, and none are REAL,
FLOAT, or DOUBLE PRECISION, and at least one is of type NUMERIC, the return
value is a NUMERIC type.
•
If the expr1 is not NULL, the return value is the same data type as expr1.
•
If the return value is of type NUMERIC or DECIMAL, it has a precision value equal
to the sum of:
°
The maximum scale of all the return values.
°
The maximum of precision minus scale value for all the return values.
However, the precision value must not exceed 18.
•
If the return value is of type NUMERIC or DECIMAL, the scale of the return value
is the minimum of:
°
The maximum scale of all the return values.
°
18 - (the maximum of precision minus scale value for all the return values).
NVL(expr1,expr2)