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:

    • expressionAny
    • value_1Any
    • result_1Any
    • value_2Any
    • result_2Any
    • default_resultAny

    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 19.13, Microsoft SQL Server 2017 (14.0), MySQL 5.6, PostgreSQL 9.3.