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 WHENbooleanExpressionTHENthenExpression[ WHENbooleanExpressionTHENthenExpression]* [ ELSEelseExpression] END

The syntax of a simple CASE expression is as follows:

CASEvalueExpressionWHENvalueExpression[ ,valueExpression]* THENthenExpression[ WHENvalueExpression[ ,valueExpression]* THENthenExpression]* [ ELSEelseExpression] 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:

CASEvalueExpressionWHENwhenOperand[ ,whenOperand]* THENthenExpression[ WHENwhenOperand[ ,whenOperand]* THENthenExpression]* [ ELSEelseExpression] END

A *whenOperand* is defined as follows:

valueExpression|comparisonOperatorexpression| IS [ NOT ] NULL | [ NOT ] LIKEcharacterExpressionWithWildCard[ ESCAPE 'escapeCharacter' ] | [ NOT ] BETWEENexpressionANDexpression| [ NOT ] INtableSubquery| [ 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