IF
Syntax
As a block
As a function
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
. The expressions to be checked are set using logical operators.
Argument types:
condition_1
—Any
result_1
—Any
condition_2
—Any
result_2
—Any
default_result
—Any
Return type: Depends on argument types
Note
Arguments result_1
, result_2
, default_result
must be of the same type.
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 21.8
, Microsoft SQL Server 2017 (14.0)
, MySQL 5.7
, Oracle Database 12c (12.1)
, PostgreSQL 9.3
, YDB
.