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

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-16
CASE (Conditional) Expression
CASE (Conditional) Expression
Considerations for CASE
Examples of CASE
The CASE expression is a conditional expression with two forms: simple and
searched.
In a simple CASE expression, NonStop SQL/MX compares a value to a sequence of
values and sets the CASE expression to the value associated with the first match—if
there is a match. If there is no match, NonStop SQL/MX returns the value specified in
the ELSE clause (which can be null).
In a searched CASE expression, NonStop SQL/MX evaluates a sequence of
conditions and sets the CASE expression to the value associated with the first
condition that is true—if there is a true condition. If there is no true condition,
NonStop SQL/MX returns the value specified in the ELSE clause (which can be null).
case-expression
specifies a value expression that is compared to the value expressions in each
WHEN clause of a simple CASE. The data type of each expression in the
WHEN clause must be comparable to the data type of case-expression.
expression-1 ... expression-n
specifies a value associated with each result-expression. If the value of an
expression in a WHEN clause matches the value of case-expression, simple
CASE returns the associated result-expression value. If there is no match,
the CASE expression returns the value expression specified in the ELSE clause, or
NULL if the ELSE value is not specified.
Simple CASE is:
CASE case-expression
WHEN expression-1 THEN {result-expression-1 | NULL}
WHEN expression-2 THEN {result-expression-2 | NULL}
...
WHEN expression-n THEN {result-expression-n | NULL}
[ELSE {result-expression | NULL}]
END
Searched CASE is:
CASE
WHEN condition-1 THEN {result-expression-1 | NULL}
WHEN condition-2 THEN {result-expression-2 | NULL}
...
WHEN condition-n THEN {result-expression-n | NULL}
[ELSE {result-expression | NULL}]
END