SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-3
Considerations—CASE Expression
target-value
if present, specifies a value or expression for which a result is returned. When you
specify target-value, you use an abbreviated form of the CASE expression,
typically used for value comparisons. The data type of each value-n in the
statement should be comparable to the data type of target-value. In addition,
the collation of each value-n should be the same or comparable to the collation
of target-value.
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 type of the result
expressions. If all the results have the same data type, the CASE expression
adopts that data type. If all the results are untyped, the CASE expression has a
CHAR (256) data type. If all the results are comparable but not identical data types,
the CASE expression adopts the data type, which is superior among the results
associated with the THEN clause or the ELSE clause, if present.

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, the value of the CASE expression is
the result associated with the ELSE clause, if present.

At least one specified result should have a nonnull 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, this
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:
CASE
WHEN target-value = value-1 THEN result-1
WHEN target-value = value-2 THEN result-2