CASE expression

See SQL expressions for more information on expressions.

Syntax

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