SQL Reference

Other Functions and Expressions

The following miscellaneous functions and expressions are supported.

CASE

CASE operates similar to conditional scripting in other programming languages, allowing it to function like an if / then / else statement or as a switch statement.

CASE sets one or more WHEN conditions to be evaluated. When a condition is evaluated as true, the database applies the specified action in a THEN clause. If no matches are found, the database applies the action in the ELSE clause. If no ELSE clause is specified and no matches are found, the result is NULL.

This evaluation creates a new column where the values are expressions inside the THEN clause.

Syntax

SQL


Arguments

Description

search_condition

An condition including a column name and a Boolean expression to evaluate.

switch_expression

A column name to be used to evaluate a switch_value.

switch_value

A value to be evaluated for the switch_expression.

expression

An expression to return based on evaluating the search_condition or switch_expression.

Example

This example uses conditional logic with CASE to categorize individuals into different groups based on their ages.

SQL


Example

This example uses switch conditions to categorize age groups.

SQL


COALESCE

Returns the first argument that is not NULL, or NULL if all arguments are NULL. Arguments must be of the same type and the return value is the most compatible type.

COALESCE can work with mixed-type numeric arguments, which means the lower precedence types are cast to the highest one. In the case of using TIMESTAMP and DATE data types, all values are cast to a timestamp.

Syntax

SQL


Argument

Data Type

Description

value [,...]

All data types are supported.

All values must be of the same type.

Values for comparison to find the value that is not NULL.

Example

SQL


Output: 13

COMMIT

Returns the most recent commit hash of the database to which the client is currently connected.

Syntax

SQL


Example

SQL


Output: cd0d53e3aa

CURRENT_DATABASE

Alias for DATABASE.

CURRENT_NODE

Returns the name of the SQL Node where the current query executes. The name of the node corresponds to the name column in the sys.nodes system catalog table.

Syntax

SQL


Example

SQL


Output: sql0

CURRENT_NODE_ID

Returns the identifier of the SQL Node where the current query executes. The name of the node corresponds to the id column in the sys.nodes system catalog table.

Syntax

SQL


Example

SQL


Output: f690c14a-4e4f-4143-8e5e-20c90b60e15c

CURRENT_SCHEMA

Returns the name of the current schema.

Syntax

SQL


Example

SQL


Output: schema@database

CURRENT_SYSTEM

Returns the name of the system. You can set the name using the ALTER SYSTEM RENAME TO SQL statement.

If you never set the system name, the system initializes the name of the system as a random identifier (UUID).

Syntax

SQL


Example

SQL


Output: 2677b88c-7f07-470c-be3e-e1bafddc1221

CURRENT_USER

Returns the user for the current connection.

Syntax

SQL


Example

SQL


Output: user@database

DATABASE

Alias for CURRENT_DATABASE.

Returns the name of the database to which the client is currently connected.

Syntax

SQL


Example

SQL


Output: test

GREATEST

Returns the largest non-NULL value of all the arguments, or NULL if all the arguments are NULL. All values must be comparable to each other.

Syntax

SQL


Argument

Data Type

Description

value [,...]

All data types are supported.



All values must either be of the same type or comparable to each other.

A series of values to compare to find the largest value of the set.

Example

SQL


Output: 13

IF_NULL

Returns the first argument that is not NULL or NULL if all arguments are NULL. Arguments must be of the same type and the return value is the most compatible type.

IF_NULL can work with mixed-type numeric arguments, which means the lower precedence types are cast to the highest one. In the case of using TIMESTAMP and DATE data types, all values are cast to a timestamp.

Syntax

SQL


Argument

Data Type

Description

value [,...]

All data types are supported.

All values must be of the same type.

Values for comparison to find the value that is not NULL.

Example

SQL


Output: 5

LEAST

Returns the smallest non-NULL value of all arguments, or NULL if all arguments are NULL. All values must be comparable to each other.

Syntax

SQL


Argument

Data Type

Description

value [,...]

All data types are supported.



All values must either be of the same type or comparable to each other.

A series of values to compare to find the smallest value of the set.

Example

SQL


Output: 5

MURMUR3

Returns a 32-bit MurmurHash3 hash of the input value as an INTEGER data type. If you specify a NULL input value, the function returns 0. This function is primarily useful for partitioning data.

Syntax

SQL


Argument

Data Type

Description

value

All SQL data types are supported.

Specified value for the MurmurHash3 hash function.

Example

SQL


Output: 0

Example

SQL


Output: 331477181

NULL_IF

Returns the NULL value if two arguments are equal; otherwise returns the first argument.

Syntax

SQL


Example

SQL


Output: 13

Example

SQL


Output: NULL

SHOW

The SHOW function enables you to explore the database and its metadata for user-defined items. For details, see Built-In Views.

Syntax

SQL


Examples

This example uses SHOW to explore databases on the system.

SQL


Output

SQL


This example explores table data.

SQL


Output

SQL


TYPE_STRIP

Returns the SQL type of the specified value.

Syntax

SQL


Argument

Data Type

Description

value

All data types are supported.

Specified value for the determination of the type.

You can specify columns, literals, and expressions.

Examples

SQL


Output: MATRIX[1][3]

SQL


Output: CHAR

SQL


Output: NULL

SQL


Output: INT[]

VERSION

Returns the version of the database to which the client is currently connected.

Syntax

SQL


Example

SQL


Output: 22.0.0-20230105.2353

ZN

If x is NULL, returns 0. Otherwise, returns x.

Syntax

SQL


Example

SQL


Output: 0

Example

SQL


Output: 12

Related Links