2.10.2.6.7. Case Expressions

The following forms of case expressions are supported: general case expressions, simple case expressions, coalesce expressions, and nullif expressions.

  • case_expression::= general_case_expression | simple_case_expression | coalesce_expression | nullif_expression

  • general_case_expression::= CASE when_clause {when_clause}* ELSE scalar_expression END

  • when_clause::= WHEN conditional_expression THEN scalar_expression

simple_case_expression::= CASE case_operand simple_when_clause {simple_when_clause}* ELSE scalar_expression END* case_operand::= state_field_path_expression | type_discriminator * simple_when_clause::= WHEN scalar_expression THEN scalar_expression * coalesce_expression::= COALESCE(scalar_expression {, scalar_expression}+) * nullif_expression::= NULLIF(scalar_expression, scalar_expression)

Examples:

UPDATE Employee e
SET e.salary =
    CASE WHEN e.rating = 1 THEN e.salary * 1.1
         WHEN e.rating = 2 THEN e.salary * 1.05
         ELSE e.salary * 1.01
    END
UPDATE Employee e
SET e.salary =
    CASE e.rating WHEN 1 THEN e.salary * 1.1
                  WHEN 2 THEN e.salary * 1.05
                  ELSE e.salary * 1.01
    END
SELECT e.name,
    CASE TYPE(e) WHEN Exempt THEN 'Exempt'
                 WHEN Contractor THEN 'Contractor'
                 WHEN Intern THEN 'Intern'
                 ELSE 'NonExempt'
    END
FROM Employee e
WHERE e.dept.name = 'Engineering'
SELECT e.name,
       f.name,
       CONCAT(CASE WHEN f.annualMiles  50000 THEN 'Platinum '
                   WHEN f.annualMiles  25000 THEN 'Gold '
                   ELSE ''
                   END,
              'Frequent Flyer')
FROM Employee e JOIN e.frequentFlierPlan f