CASE
statements evaluate one or more conditions and return a result when the first condition is met, or a default result if none of the conditions are met.
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.
Sample usage
A common use for CASE
is to create new categories or groupings of data. For example, to group selected country values into a Sales Region dimension, you might create a CASE
statement like this:
CASE WHEN Country IN ("USA","Canada","Mexico") THEN "North America" WHEN Country IN ("England","France") THEN "Europe" ELSE "Other" END
Syntax
CASE WHEN condition THEN result [WHEN condition THEN result] [...] [ELSE else_result] END
Parameters
-
condition
- A logical field or expression.WHEN
clauses evaluatecondition
and return true if the specified condition is met, or false if the condition isn't met. -
result
- A field or expression of any type. EachWHEN
clause must have a matchingTHEN
clause, which specifies the results if thatWHEN condition
is true. If there are multipleWHEN
clauses, theCASE
statement returns theresult
for the first truecondition
. else_result
(optional) - A field or expression of any type. TheELSE
else_result
clause specifies a default result for theCASE
statement. This clause is returned if none of theWHEN
clauses are true. If aCASE
statement has noELSE
clause, and none of theWHEN
clauses are true, theCASE
statement returnsNULL
.
A CASE
statement can have only one ELSE
clause.
Note: All of the THEN
clauses in a CASE
statement must return the same data type. For example, if the first THEN
clause returns the Text data type, additional THEN
clauses must also return the Text data type.
How searched CASE
works
A searched CASE
statement begins with the CASE
keyword and ends with the END
keyword. In between, you'll have a number of sections or clauses:
WHEN
: A condition that you want to evaluate. You can have multipleWHEN
clauses in a singleCASE
statement.THEN
: The result to return if theWHEN
clause's condition is true. You must have oneTHEN
clause for eachWHEN
clause in yourCASE
statement.ELSE:
Optional. If none of theWHEN
clause conditions are true,CASE
returns the value in theELSE
clause, orNULL
if noELSE
clause is specified.
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
.
Examples
Check for inequality
CASE WHEN Medium != "cpc" THEN "free" ELSE "paid" END
Classify numeric dimension values into discrete buckets
For example, you can separate orders into "Small", "Medium", or "Large" based on order amount:
CASE WHEN Amount < 20 THEN "Small" WHEN Amount >= 20 and Amount < 100 THEN "Medium" WHEN Amount >= 100 THEN "Large" END
Evaluate a logical AND condition
CASE WHEN Country ISO Code = "US" AND Medium = "cpc" THEN "US - Paid" ELSE "other" END
Evaluate a logical AND/OR condition
CASE WHEN REGEXP_MATCH(Video Title, ".*Google Analytics*") AND is_livestream = TRUE OR Video Length > 600 THEN "GA Livestream or long video" END
Return a different field or value depending on a parameter value
Example 1: Return a dimension based on the parameter value selected. You can use this technique to let users switch the breakdown dimension used by a chart.
CASE WHEN Breakdown = "Product Category" THEN Product Category WHEN Breakdown = "Store" THEN Store END
Note that you could also write this using simple CASE syntax:
CASE Breakdown WHEN "Product Category" THEN Product Category WHEN "Store" THEN Store END
Example 2: Return a different metric based on a parameter value.
CASE WHEN Show Adjusted Cost = TRUE THEN Cost + Adjustments ELSE Cost END
Nested CASE
statements
You can nest CASE
statements to create more complex branching logic:
CASE WHEN REGEXP_CONTAINS(SKU, "Hats") THEN CASE WHEN Color = "blue" THEN "BLUE HAT" ELSE "JUST A HAT" END ELSE "NOT A HAT" END