Case Expressions

Case expressions are expressions that change based on a condition, similar to the case keyword of the Java programming language. The CASE keyword indicates the start of a case expression, and the expression is terminated by the END keyword. The WHEN and THEN keyword define individual conditions, and the ELSE keyword defines the default condition should none of the other conditions be satisfied.

Example 21–1 Using Case Expressions in the Query Language

The following query selects the name of a person and a conditional string, depending on the subtype of the Person entity. If the subtype is Student, it returns the string kid. If the subtype is Guardian or Staff, it returns adult. If the entity is some other subtype of Person, the string unknown is returned.

SELECT p.name
CASE TYPE(p)
  WHEN Student THEN 'kid'
  WHEN Guardian THEN 'adult'
  WHEN Staff THEN 'adult'
  ELSE 'unknown'
END
FROM Person p

The following query sets a discount for different types of customers. Gold-level customers get a 20% discount, silver-level customers get a 15% discount, bronze-level customers get a 10% discount, and everyone else gets a 5% discount.

UPDATE Customer c
SET c.discount = 
  CASE c.level
    WHEN 'Gold' THEN 20
    WHEN 'SILVER' THEN 15
    WHEN 'Bronze' THEN 10
    ELSE 5
  END