DataLens

CASE

Syntax

CASE expression    WHEN value_1 THEN result_1    [ WHEN value_2 THEN result_2      ... ]    ELSE default_resultEND
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 — Any
  • value_1 — Any
  • result_1 — Any
  • value_2 — Any
  • result_2 — Any
  • default_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 .
unitcase_functioncase_statement
's'11
'm'6060
'h'36003600

Data source support

ClickHouse 21.8, Microsoft SQL Server 2017 (14.0), MySQL 5.7, Oracle Database 12c (12.1), PostgreSQL 9.3, YDB.

Previous
Overview
Next
IF