Skip to content

Conditional Expression Operations#

CASE#

Syntax

CASE WHEN <condition1> THEN <result1> [...] [ELSE <result3>] END

Description

Evaluates a list of conditions until one is TRUE and returns the result.

View examples

Formula Editor Example

CASE WHEN 1=1 THEN 'one' ELSE 'undefined' END
column1 result
1 one
3 undefined
4 undefined

SQL Editor Example

select
    column1,
    case
      when column1=4 then 'four'
      when column1=6 then 'six'
      else 'undefined'
    end as result
from (values(4),(6),(8)) v;
column1 result
4 four
6 six
8 undefined

COALESCE#

Syntax

COALESCE( <expr1> , <expr2> [ , ... , <exprN> ] )

Description

Returns the first non-NULL expression among its arguments, or NULL if all its arguments are NULL. Note that all arguments must have the same data type.

View examples

Formula Editor Examples

COALESCE(1, 2, 3)
column1 column2 column3 coalesce(column1, column2, column3)
1 2 3 1 
COALESCE(null, 2, 3)
column1 column2 column3 coalesce(column1, column2, column3)
null 2 3 2

SQL Editor Example

select column1, column2, column3, coalesce(column1, column2, column3)
from (values
  (1,    2,    3   ),
  (null, 2,    3   ),
  (null, null, 3   ),
  (null, null, null),
  ) v;
column1 column2 column3 COALESCE(column1, column2, column3)
1 2 3 1
NULL 2 3 2
NULL NULL 3 3
NULL NULL NULL NULL

GREATEST#

Syntax

GREATEST( <expr1> [ , <expr2> ... ] )

Description

Returns the largest value from a list of expressions. If any of the argument values is NULL, the result is NULL.

View examples

Formula Editor Example

GREATEST(expr1, expr2)

SQL Editor Example

create table test_table_1_greatest (col_1 integer, col_2 integer,
    col_3 integer, col_4 float);
insert into test_table_1_greatest (col_1, col_2, col_3, col_4) values
    (1, 2,    3,  4.00),
    (2, 4,   -1, -2.00),
    (3, 6, null, 13.45);

select
    col_1,
    col_2,
    col_3,
  greatest(col_1, col_2, col_3) as greatest
  from test_table_1_greatest
  order by col_1;
col_1 col_2 col_3 GREATEST
1 2 3 3
2 4 -1 4
3 6 NULL NULL

IFF#

Syntax

IFF( <condition> , <expr1> , <expr2> )

Description

If the condition evaluates to TRUE, it returns expr1, otherwise returns expr2.

View examples

Formula Editor Example

Given the following table:

Condition_1
true
false

Applied formula:

IFF(Condition_1, 'yes', 'no')

Output:

Condition_1_IFF
yes
no

SQL Editor Example

select iff(true, 'true', 'false');
IFF(TRUE, 'TRUE', 'FALSE')
true

IFNULL#

Syntax

IFNULL( <expr1> , <expr2> )

Description

If expression 1 is NULL, returns expression 2, otherwise returns expression 1.

View examples

Formula Editor Example

Given the following table:

Condition_1 Condition_2
0 3
NULL 2
3 NULL
NULL NULL

Applied formula:

IFNULL(Condition_1, 'Condition_2')

Output:

Condition_1_IFFNULL
0
2
3
NULL

SQL Editor Example

select a, b, ifnull(a,b), ifnull(b,a) from i;
a b ifnull(a,b) ifnull(b,a)
0 5 0 5
0 [NULL] 0 0
[NULL] 5 5 5
[NULL] [NULL] [NULL] [NULL]

LEAST#

Syntax

LEAST( <expr1> [ , <expr2> ... ] )

Description

Returns the smallest value from a list of expressions. If any of the argument values is NULL, the result is NULL.

View examples

Formula Editor Example

Given the following table:

Column_1 Column_2 Column_3
0 3 5
-1 2 8
3 NULL 0
NULL NULL NULL

Applied formula:

LEAST(Column_1, Column_2, Column_3)

Output:

Column_LEAST
0
-1
NULL
NULL

SQL Editor Example

select least(1, 3, 0, 4);
LEAST(1, 3, 0, 4)
0 
select
    col_1,
    col_2,
    col_3,
    least(col_1, col_2, col_3) as least
  from (select 1 as col_1, 2 as col_2, 3 as col_3
      union all
      select 2, 4, -1
      union all
      select 3, 6, null
     );
COL_1 COL_2 COL_3 LEAST
1 2 3 1
2 4 -1 -1
3 6 NULL NULL

NULLIF#

Syntax

NULLIF( <expr1> , <expr2> )

Description

Returns NULL if expression 1 is equal to expression 2, otherwise returns expression 1. The data type of the returned value is the data type of expression 1.

View examples

Formula Editor Example

Given the following table:

Column_1 Column_2
3 3
-1 2
3 NULL
NULL NULL

Applied formula:

NULLIF(Column_1, Column_2)

Output:

Column_NULLIF
NULL
-1
3
NULL

SQL Editor Example

select a, b, nullif(a,b) from i;
a b nullif(a,b)
0 0 [NULL]
0 1 0
0 [NULL] 0
1 0 1
1 1 [NULL]
1 [NULL] 1
[NULL] 0 [NULL]
[NULL] 1 [NULL]
[NULL] [NULL] [NULL]

NVL#

Syntax

NVL( <expr1> , <expr2> )

Description

If expression 1 is NULL, returns expression 2, otherwise returns expression 1.

View examples

Formula Editor Example

Given the following table:

Column_1 Column_2
0 3
NULL 2
3 NULL
NULL NULL

Applied formula:

NVL(Column_1, Column_2)

Output:

Column_NVL
0
2
3
NULL

SQL Editor Example

select nvl('food', 'bard') as col1, nvl(null, 3.14) as col2;
COL1 COL2
food 3.14

NVL2#

Syntax

NVL( <expr1> , <expr2>, <expr3> )

Description

Returns values depending on whether the first input is NULL. All three expressions should have the same (or compatible) data type.

View examples

Formula Editor Example

Given the following table:

Column_1 Column_2 Column_3
0 3 5
NULL 2 8
3 NULL 0
NULL NULL NULL

Applied formula:

NVL2(Column_1, Column_2, Column_3)

Output:

Column_NVL2
5
8
NULL
NULL

SQL Editor Example

select a, b, c, nvl2(a, b, c) from i2;
A B C NVL2(A, B, C)
0 5 3 5
0 5 [NULL] 5
0 [NULL] 3 [NULL]
0 [NULL] [NULL] [NULL]
[NULL] 5 3 3
[NULL] 5 [NULL] [NULL]
[NULL] [NULL] 3 3
[NULL] [NULL] [NULL] [NULL]