CASE

Syntax

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 — 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 .
unit case_function case_statement
's' 1 1
'm' 60 60
'h' 3600 3600

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