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::=
CASEwhen_clause {when_clause}*ELSEscalar_expressionEND -
when_clause::=
WHENconditional_expressionTHENscalar_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