CASE
Definition
The CASE function allows you to perform conditional statements in BigQuery. This is a more complex implementation of IF.
| colour | primary_colour |
|---|---|
| blue | true |
| pink | false |
| black | false |
Practical Info
- If you leave out an
else_result, then the query will returnNULLif not matched to theresult resultandelse_resultmust be coercible to a common supertype.
Common Questions
How to create multiple 'THEN' clauses for a case statement?
To add multiple expression -> result pairings, simply add another WHEN ... THEN clause (without commas) to your function.
Using the example data above, we could use the following query to identify which are the primary colours:
How to compare strings ignoring case?
To account for strings being of different cases, we can use UPPER and LOWER to standardise all of the cases in both our expressions to match, and the match criteria.
Using the data above, we could modify our case statement as follows:
How to compare dates using a case statement?
As long as your expressions are of the right type you can use comparison operators (e.g. >, <, >=) to compare dates:
Note: BETWEEN is inclusive so it will return true for both 2020-10-01 AND 2021-09-30.
| date_col | fiscal_date |
|---|---|
| 2020-10-01 | FY 2021 |
| 2021-09-30 | FY 2021 |
| 2019-10-01 | pre FY 2021 |
How to Group By / Aggregate by a Case function
To group by the results of a CASE function, just make sure you add the name of the column to your GROUP BY clause.
| f0_ | Region |
|---|---|
| -1 | East |
| 4 | West |
To aggregate by a case column, make sure your results are numbers:
| east_total | west_total |
|---|---|
| -1 | 4 |
Troubleshooting Common Errors
Type mismatch for operands of = operator: number and string.
This happens when your conditional expression is invalid. For example:
You can resolve this by making sure your condition column and criteria are compatible types:
Case expression returns with mixed types are not supported.
This error occurs when your result types are different. In this example returning 1 in one case and 'hello' in another won't work.
CASE WHEN number_column = 'string' ....To resolve this, make sure your result types are compatible and coercible.
Related Pages
- Data Types in Standard SQL