NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-3
Considerations—CASE Expression
value-1 through value-n
specifies a value associated with result-n. If the value matches target-
value, the CASE expression returns the associated result.
Considerations—CASE Expression
•
The data type 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 all results are untyped, the CASE expression has a CHAR (256)
data type. If the results have comparable but not identical data types, the CASE
expression adopts the data type of the first typed result in the result list.
•
If you plan to use the value of the CASE expression in a comparison (as in a
WHERE clause), the character set and collation associated with the CASE
expression should be the same or comparable to those associated with the
comparison expression.
•
If none of the search-conditions are true, then the value of the CASE
expression is the result associated with the ELSE clause, if present.
•
At least one specified result should have a non-null value.
•
A SELECT clause cannot appear as part of a search-condition if the CASE
expression is part of the select list in a SELECT statement. For example, the
following statement is not valid:
SELECT CASE WHEN a IN (SELECT b FROM T1) THEN 1
WHEN b IN (SELECT c FROM T1) THEN 2
ELSE 3
END
FROM table1;
You can, however, use the CASE expression in the WHERE clause of a SELECT
statement.
•
Use of the abbreviated form of the CASE expression, CASE value..., is equivalent
to using the following:
CASE
WHEN target-value = value-1 THEN result-1
WHEN target-value = value-2 THEN result-2
WHEN target-value = value-3 THEN result-3
...
WHEN target-value = value-n THEN result-n
ELSE value-x
END