CASE expression

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

Syntax

You can place a CASE expression anywhere an expression is allowed. It chooses an expression to evaluate based on a boolean test.

CASE 
  WHEN booleanExpression THEN thenExpression 
  [ WHEN booleanExpression THEN thenExpression ]...
  ELSE elseExpression 
END

ThenExpression and elseExpression are both expressions that must be type-compatible. For built-in types, this means that the types must be the same or a built-in broadening conversion must exist between the types.

Example

-- returns 3
VALUES CASE WHEN 1=1 THEN 3 ELSE 4 END

-- returns 7
VALUES
   CASE
      WHEN 1 = 2 THEN 3
      WHEN 4 = 5 THEN 6
      ELSE 7
   END
Related concepts
Dynamic parameters
Related reference
SelectExpression
TableExpression
NEXT VALUE FOR expression
VALUES expression
Expression precedence
Boolean expressions
NULLIF function
COALESCE function