Oracle CASE Expressions

CASE Expressions
Topics

• Simple CASE Expression
• Searched CASE Expression

1. Simple CASE Expression

For this explanation, assume that a simple CASE expression has this syntax:

CASE selector
WHEN { selector_value_1a | dangling_predicate_1a }
  [ , ..., { selector_value_1n | dangling_predicate_1n } ] THEN result_1
WHEN { selector_value_2a | dangling_predicate_2a }
  [ , ..., { selector_value_2n | dangling_predicate_2n } ] THEN result_2
...
WHEN { selector_value_na | dangling_predicate_na }
  [ , ..., { selector_value_nn | dangling_predicate_nn }] THEN result_n
[ ELSE
  else_result ]
END;

The selector is an expression (typically a single variable). Each selector_value and each result can be either a literal or an expression. A dangling_predicate can also be used either instead of or in combination with one or multiple selector_values. At least one result must not be the literal NULL.

A dangling_predicate is an ordinary expression with its left operand missing, for example < 2. Using a dangling_predicate allows for more complicated comparisons that would otherwise require a searched CASE statement.

The simple CASE expression returns the first result for which the selector_value or dangling_predicate matches selector. Remaining expressions are not evaluated. If no selector_value or dangling_predicate matches selector, the CASE expression returns else_result if it exists and NULL otherwise.

A list of comma-separated selector_values and or dangling_predicates can be used with each WHEN clause if multiple choices map to a single result. As with selector_values and dangling_predicates listed in separate WHEN clauses, only the first selector_value or dangling_predicate to match the selector is evaluated.

1.1 Simple CASE Expression

This example assigns the value of a simple CASE expression to the variable appraisal. The selector is grade.

DECLARE
  grade CHAR(1) := 'B';
  appraisal VARCHAR2(20);
BEGIN
  appraisal :=
    CASE grade
      WHEN 'A' THEN