CASE (simple)

Simple CASE returns a result based on the value of a single input expression, or a default result if none of the comparison values match.

See also: IF.

Sample usage

Replace payment codes with intuitive names:

    CASE Payment Type
        WHEN "CC" THEN "Credit Card"
        WHEN "DC" THEN "Debit Card"
        WHEN "GC" THEN "Gift Card"
        WHEN "CA" THEN "Cash"
        ELSE "Other"
    END

Syntax

    CASE input_expression
        WHEN expression_to_match THEN result
        [WHEN expression_to_match THEN result]
        [...]
        [ELSE else_result]
    END

Parameters

  • input_expression - Any valid field or expression.
  • [expression_to_match - Any valid field or expression. The WHEN clause compares input_expression to input_expression and returns true if the two are equal, or false if they aren't.]{#when-conditions}
  • result - Any valid field or expression. Each WHEN clause must have a matching THEN clause, which specifies the results to return if that condition is true. If there are multiple WHEN clauses, the CASE statement returns the result for the first true clause.
  • else_result (optional) - Any valid field or expression. The ELSE else_result clause specifies a default result for the CASE statement. This clause is returned if none of the WHEN clauses are true. If a CASE statement has no ELSE clause, and none of the WHEN clauses are true, the CASE statement returns NULL.

How simple CASE works

A simple CASE statement consists of the following elements:

  • The CASE keyword, followed by an input expression.
  • WHEN : the value against which to compare the input_expression : if the value equals the input_expression, then this clause is true. You can have multiple WHEN clauses in a single CASE statement.
  • THEN : the result to return if the WHEN clause's condition is true. You must have one THEN clause for each WHEN clause in your CASE statement.
  • ELSE : Optional. If none of the WHEN clause conditions are true, CASE returns the value in the ELSE clause, or NULL if no ELSE clause is specified.
  • The END keyword.

CASE evaluates each successive WHEN clause and returns the first result where the condition is true. Any remaining WHEN clauses and the ELSE result are not evaluated. If all WHEN conditions are false or NULL, CASE returns the ELSE result, or if no ELSE clause is present, returns NULL.

Example

Provide customized links for your premium customers:

    CASE Premium Status
        WHEN "Platinum" THEN CONCAT(Site URL, "platinum_welcome.html")
        WHEN "Gold" THEN CONCAT(Site URL, "gold_welcome.html")
        WHEN "Silver" THEN CONCAT(Site URL, "silver_welcome.html")
        ELSE CONCAT(Site URL, "welcome.html")
    END