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