IF

Syntax

IF condition_1
    THEN result_1
[ ELSEIF condition_2
    THEN result_2
  ... ]
ELSE
    default_result
END
IF(
    condition_1, result_1,
  [ condition_2, result_2,
    ... ]
    default_result
)

Description

Checks conditional expressions condition_1, result_1, ... and returns the matching result for the first condition found to be TRUE. IF all conditional expressions are FALSE, it returns default_result.

Argument types:

  • condition_1Any
  • result_1Any
  • condition_2Any
  • result_2Any
  • default_resultAny

Return type: Depends on argument types

Examples

IF
    [Profit] > 100
        THEN "High"
    ELSEIF [Profit] > 25
        THEN "Medium"
    ELSE "Low"
END
Example with data table

Formulas:

  • sales: [sales] ;
  • if_function: IF(ZN([sales]) < 100, "Less than 100", [sales] < 1000, "100 - 1000", "1000 and greater") ;
  • if_statement: IF ZN([sales]) < 100 THEN "Less than 100" ELSEIF [sales] < 1000 THEN "100 - 1000" ELSE "1000 and greater" END .
sales if_function if_statement
432.40 '100 - 1000' '100 - 1000'
77.00 'Less than 100' 'Less than 100'
12000.00 '1000 and greater' '1000 and greater'
NULL 'Less than 100' 'Less than 100'
34.25 'Less than 100' 'Less than 100'
128.00 '100 - 1000' '100 - 1000'
0.00 'Less than 100' 'Less than 100'
NULL 'Less than 100' 'Less than 100'

Data source support

ClickHouse 19.13, Microsoft SQL Server 2017 (14.0), MySQL 5.6, PostgreSQL 9.3.

Previous
Next