Conditional functions¶
Conditional functions allow encoding conditional expressions and make decisions based on the values found in a column.
CASE expr¶
Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN
Compare values in a column to each consecutive WHEN clause
and returns the first match. If no match found in any of the
WHEN clauses then, if present, the value from the ELSE
clause is returned. If the ELSE is not present nor any match
is found, a NULL is returned.
Example¶
SELECT CASE <col_1>
WHEN <literal_1> THEN <result_1>
WHEN <literal_2> THEN <result_2>
ELSE <result_3>
END
FROM <table_name>
CASE¶
Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN
Compare values in a column to each consecutive WHEN clause
and returns the first match. If no match found in any of the
WHEN clauses then, if present, the value from the ELSE
clause is returned. If the ELSE is not present nor any match
is found, a NULL is returned.
The difference between this function and CASE expr is that the conditions can mix more than one column and different operators.
Example¶
SELECT CASE WHEN <col_1> IN (<literal_1>, <literal_2) THEN <result_1>
WHEN <col_2> > 0 THEN <result_2>
ELSE <result_3>
END
FROM <table_name>
COALESCE¶
Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN
This function returns a default if the value in the column is NULL.
Note
The column types and/or literals used in this function need to be of compatible data types or types that can be converted to a compatible type.
Example¶
The simplest example returns a literal for every NULL in a column.
SELECT COALESCE(<col_1>, <literal>)
FROM <table_name>
More than one column can be included and the function will return
the first value from the series that is not NULL.
Note
The list of columns will be evaluated left-to-right thus,
i.e. in an expression COALESCE(<col_1, <col_2>, <literal>),
if col_1 is NULL but col_2 is not, the value
found in col_2 will be returned, not the literal.
SELECT COALESCE(<col_1>, <col_2>, <literal>)
FROM <table_name>
NULLIF¶
Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN
This function returns a NULL if the value matches the expression.
Note
The column types and/or literals used in this function need to be of compatible data types or types that can be converted to a compatible type.
See also
Example¶
SELECT NULLIF(<col_1>, <literal_1>)
FROM <table_name>
NVL¶
Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN
This function returns a default if the value in the column is NULL.
Note
The column types and/or literals used in this function need to be of compatible data types or types that can be converted to a compatible type.
Example¶
SELECT NVL(<col_1>, <literal>)
FROM <table_name>