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-27
COALESCE Function
COALESCE Function
Returns the value of the first expression in the list that is NOT NULL or if all the
expressions in the list result in NULL, the function returns NULL.
expr1, expr2 , expr3 and so on are SQL value expressions of comparable data
types.
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 type, the
return value is a variable-length character string with the 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, none are REAL,
FLOAT, or DOUBLE PRECISION, and at least one is of type NUMERIC, the return
value is a NUMERIC type.
•
The last expression in the COALESCE function cannot be NULL. This is a
NonStop SQL/MX restriction.
•
If the return value is of type NUMERIC or DECIMAL, the value 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).
•
The COALESCE function is internally transformed into the SQL/MX
CASE…WHEN…ELSE…END function, and therefore all typing rules that apply to
the CASE…WHEN…ELSE…END also apply to this function.
COALESCE (expr1, expr2[,expr3 ...])