Skip to main content

Logical Expressions


COALESCE

Returns the first non-null value in a list of argument.

Syntax

COALESCE ( expression, expression [ ,...n ] ) 

Arguments

expression
A column expression or value with matching data types.

Return Type

Returns the type of the first non-null value.

Examples

Examples in this section use the example blockchain.

The following example returns the first non-null value for real, integer, and 0.

SELECT COALESCE(real, FLOAT32(integer), FLOAT32(0)) FROM example;

Response:

COALESCE(real, FLOAT32(integer), FLOAT32(0)) [FLOAT32]
-1
0
1
2

NULLIF

Returns null if the two column expression are equal.

Syntax

NULLIF ( expression , expression ) 

Arguments

expression
A column expression or value with matching data types.

Return Type

Returns NULL if the two expressions are equivalent, otherwise returns the first expression.

Examples

Examples in this section use the example blockchain.

The following example returns NULL if the value from column b is true.

SELECT NULLIF(b, true) FROM example;

Response:

NULLIF(b, true) [BOOL]
NULL
false
NULL
false

CASE

Evaluates a list of conditions and returns one of multiple possible result expressions.

The CASE expression has two formats:

  • The simple CASE expression compares an expression to a set of simple expressions to determine the result.

  • The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

Syntax

--Simple CASE expression:   
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END

--Searched CASE expression:
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END

Arguments

input_expression
Is the expression evaluated when the simple CASE format is used.

WHEN when_expression
Is a simple expression to which input_expression is compared when the simple CASE format is used. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

THEN result_expression Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE.

ELSE else_result_expression Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

WHEN Boolean_expression Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.

Return Type

Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

Examples

Examples in this section use the example blockchain.

The following example uses a simple CASE expression and compares values in the integer column.

SELECT (CASE 
integer
WHEN 0
THEN true
WHEN 2
THEN false
)
AS expr FROM example;

Response:

expr [INT64]
NULL
true
NULL
FALSE

The following example uses a searched CASE expression and evaluates the boolean operations provided.

SELECT (CASE 
WHEN b
THEN 1
WHEN NOT b
THEN 2
ELSE 0)
AS expr FROM example;

Response:

expr [INT64]
0
2
1
2