The CASE expression can be used for conditional expressions in Derby.
See SQL expressions for more information on expressions.
You can place a CASE expression anywhere an expression is allowed. It chooses an expression to evaluate based on a boolean test.
Derby 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 Boolean expressions.
For all types of CASE expressions, if an ELSE clause is not specified, ELSE NULL is implicit.
-- searched CASE expression
-- returns 3
VALUES CASE WHEN 1=1 THEN 3 ELSE 4 END
-- simple CASE expression, equivalent to previous expression
-- returns 3
VALUES CASE 1 WHEN 1 THEN 3 ELSE 4 END
-- searched CASE expression
-- returns 7
VALUES
CASE
WHEN 1 = 2 THEN 3
WHEN 4 = 5 THEN 6
ELSE 7
END
-- simple CASE expression
-- returns 'two'
VALUES
CASE 1+1
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'many'
END
-- simple CASE expression
-- returns 'odd', 'even', 'big'
SELECT
CASE X
WHEN 1, 3, 5, 7, 9 THEN 'odd'
WHEN 2, 4, 6, 8, 10 THEN 'even'
ELSE 'big'
END
FROM
(VALUES 5, 8, 12) AS V(X)
-- extended CASE expression
-- returns ('long', 182), ('medium', 340), ('short', 20)
SELECT DISTANCE, COUNT(*)
FROM (SELECT
CASE MILES
WHEN < 250 THEN 'short'
WHEN BETWEEN 250 AND 2000 THEN 'medium'
WHEN > 2000 THEN 'long'
END
FROM FLIGHTS) AS F(DISTANCE)
GROUP BY DISTANCE