CASE
defines one or more conditions and returns a result when a condition is met. You can define a default result if none of the conditions are met.
Note: there two forms of the CASE
statement: searched CASE
and simple CASE
. Searched CASE
is more powerful, while simple CASE
statements are simpler to construct.
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
expression 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
WHEN condition
WHEN
conditions evaluate your data and return true if the specified condition is met, or false if it isn't. You can use any valid boolean expression as the WHEN
conditions.
THEN result
Each WHEN
condition must have a matching THEN
clause, which specifies the results if that condition is true. If there are multiple WHEN
conditions, the CASE
expression returns the result for the first true condition.
ELSE else_result
The optional ELSE
clause specifies a default result. This clause is returned if none of the WHEN
clauses are true. If a CASE
expression has no ELSE
clause, and none of the WHEN clauses are true, the statement returns NULL
.
A CASE
expression can only have one ELSE
clause.
Notes
All of the possible results in a THEN
and ELSE
clauses must be of the same type.
For example, if the first THEN
clause returns text, additional THEN
clauses, as well as the ELSE
clause, must also return text.
How searched CASE
works
A searched CASE
expression 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
expression.THEN
: The result to return if theWHEN
clause's condition is true. You must have oneTHEN
clause for eachWHEN
clause in yourCASE
expression.ELSE:
Optional. If none of theWHEN
clause conditions are true,CASE
returns the value in theELSE
clause, orNULL
is returned 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
Evaluate a parameter
For example, you can set a "Sales Target" parameter and adjust it to see which orders met the target:
CASE WHEN Qty Sold >= Sales target parameter THEN 1 ELSE 0 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