SyntaxYou can place a CASE expression
anywhere an expression is allowed. It chooses an expression to evaluate based
on a boolean test.
supports three kinds
of CASE expressions, which we refer to as a
searched CASE expression, a simple CASE expression,
and an extended CASE expression.
The syntax of a searched CASE expression is as follows:
CASE
WHEN booleanExpression THEN thenExpression
[ WHEN booleanExpression THEN thenExpression ]*
[ ELSE elseExpression ]
END
The syntax of a simple CASE expression is as follows:
CASE valueExpression
WHEN valueExpression [ , valueExpression ]* THEN thenExpression
[ WHEN valueExpression [ , valueExpression ]* THEN thenExpression ]*
[ ELSE elseExpression ]
END
A valueExpression is an expression that resolves to a single
value.
For both searched and simple CASE expressions, both thenExpression and
elseExpression are defined as follows:
NULL | valueExpression
The thenExpression and elseExpression must be type-compatible.
For built-in types, this means that the types must be the same or that a
built-in broadening conversion must exist between the types.
The syntax of an extended CASE expression is as follows:
CASE valueExpression
WHEN whenOperand [ , whenOperand ]* THEN thenExpression
[ WHEN whenOperand [ , whenOperand ]* THEN thenExpression ]*
[ ELSE elseExpression ]
END
A whenOperand is defined as follows:
valueExpression |
comparisonOperator expression |
IS [ NOT ] NULL |
[ NOT ] LIKE characterExpressionWithWildCard [ ESCAPE 'escapeCharacter' ] |
[ NOT ] BETWEEN expression AND expression |
[ NOT ] IN tableSubquery |
[ NOT ] IN ( expression [, expression ]* ) |
comparisonOperator { ALL | ANY | SOME } tableSubquery
A comparisonOperator is defined as follows:
{ < | = | > | <= | >= | <> }
For details on LIKE expressions, see .
For all types of CASE expressions, if an ELSE clause is not specified,
ELSE NULL is implicit.