CASE
Syntax
As a block
As a function
CASE expression
WHEN value_1 THEN result_1
[ WHEN value_2 THEN result_2
... ]
ELSE default_result
END
CASE(
expression,
value_1, result_1,
[ value_2, result_2,
... ]
default_result
)
Description
Compares expression to value_1, value_2, ... consecutively and returns the corresponding result for the first match. If no match is found, it returns default_result.
Argument types:
expression—Anyvalue_1—Anyresult_1—Anyvalue_2—Anyresult_2—Anydefault_result—Any
Return type: Same type as (result_1, result_2, default_result)
Note
Arguments (result_1, result_2, default_result) must be of the same type.
Examples
CASE (
[country],
"AO", "Angola",
"AU", "Australia",
"BY", "Belarus",
"CA", "Canada",
"TT", "Trinidad and Tobago",
"Other Country"
)
CASE [country]
WHEN "AO" THEN "Angola"
WHEN "AU" THEN "Australia"
WHEN "BY" THEN "Belarus"
WHEN "CA" THEN "Canada"
WHEN "TT" THEN "Trinidad and Tobago"
ELSE "Other Country"
END
Example with data table
Formulas:
- unit:
[unit]; - case_function:
CASE([unit], "s", 1, "m", 60, "h", 3600, 0); - case_statement:
CASE [unit] WHEN "s" THEN 1 WHEN "m" THEN 60 WHEN "h" THEN 3600 ELSE 0 END.
| unit | case_function | case_statement |
|---|---|---|
's' |
1 |
1 |
'm' |
60 |
60 |
'h' |
3600 |
3600 |
Data source support
ClickHouse 21.8, Files, Google Sheets, Microsoft SQL Server 2017 (14.0), MySQL 5.7, Oracle Database 12c (12.1), PostgreSQL 9.3, Yandex Documents, YDB.