SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-17
Considerations for CASE
result-expression-1 ... result-expression-n
specifies the result value expression associated with each expression in a
WHEN clause of a simple CASE, or with each condition in a WHEN clause of a
searched CASE. All of the result-expressions must have comparable data
types, and at least one of the result-expressions must return nonnull.
result-expression
follows the ELSE keyword and specifies the value returned if none of the
expressions in the WHEN clause of a simple CASE are equal to the case
expression, or if none of the conditions in the WHEN clause of a searched CASE
are true. If the ELSE result-expression clause is not specified, CASE returns
NULL. The data type of result-expression must be comparable to the other
results.
condition-1 ... condition-n
specifies conditions to test for in a searched CASE. If a condition is true, the
CASE expression returns the associated result-expression value. If no
condition is true, the CASE expression returns the value expression specified in
the ELSE clause, or NULL if the ELSE value is not specified.
Considerations for CASE
Data Type of the CASE Expression
The data type of the result of the CASE expression depends on the data types of the
result expressions. If the results all have the same data type, the CASE expression
adopts that data type. If the results have comparable but not identical data types, the
CASE expression adopts the data type of the union of the result expressions. This
result data type is determined in these ways.
Character Data Type
If any data type of the result expressions is variable-length character string, the result
data type is variable-length character string with maximum length equal to the
maximum length of the result expressions.
Otherwise, if none of the data types is variable-length character string, the result data
type is fixed-length character string with length equal to the maximum of the lengths of
the result expressions.
Numeric Data Type
If all of the data types of the result expressions are exact numeric, the result data type
is exact numeric with precision and scale equal to the maximum of the precisions and
scales of the result expressions.