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.

Note: there are two forms of the CASE statement: searched CASE and simple CASE. Searched CASE statements allow you to use more sophisticated logic, while simple CASE statements are simpler to construct.

See also: IF.

In this article:

• Sample usage
• Syntax
• How simple CASE works
• Example
• Related resources

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.
• 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.

Note: All of the THEN clauses in a CASE statement must return the same type of result. For example, if the first THEN clause returns the Text data type, additional THEN clauses must also return the Text data type.

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

Related resources

• Calculated fields
• Looker Studio function list
• google/RE2 Github documentation