# CASE expression

The CASE expression can be used for conditional expressions in Derby.

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.

## Example

    -- 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

Parent topic: SQL expressions
Related concepts
Dynamic parameters
Related reference
selectExpression
tableExpression
NEXT VALUE FOR expression
VALUES expression
Expression precedence
Boolean expressions
NULLIF function
COALESCE function