SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
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 non-null.
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.
If any one of the result expressions is a character string constant, the result data type
is a variable-length character string with a maximum length equal to the maximum
length of the result expressions.
However, if either of the data types is not a variable-length character string and the
result expressions is not a character string constant, the result data type is fixed-length
character string with a length equal to the maximum of the lengths of the result
expressions.