Other Functions and Expressions - Ocient Documentation

The System supports these miscellaneous functions and expressions.

CASE

CASE operates similarly 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

--To use CASE like an if / then statement:

CASE <caseCondition> ELSE expression | NULL END

<caseCondition> ::=
WHEN search_condition | expression THEN expression | NULL

--To use CASE like a switch statement:
CASE switch_expression <switchCondition> ELSE expression | NULL END

<switchCondition> ::=
WHEN switch_value THEN expression | NULL

Arguments	Description
`search_condition`	A 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

SELECT age,
  CASE
    WHEN age < 13 THEN 'child'
    WHEN age < 19 THEN 'teen'
    ELSE 'adult'
  END AS age_grp
FROM sys.people
ORDER BY age;

Example This example uses switch conditions to categorize age groups.

SQL

SELECT age,
  CASE age
    WHEN 13 THEN 'thirteen'
    WHEN 14 THEN 'fourteen'
    WHEN 15 THEN 'fifteen'
    WHEN 16 THEN 'sixteen'
    WHEN 17 THEN 'seventeen'
    WHEN 18 THEN 'eighteen'
    WHEN 19 THEN 'nineteen'
    ELSE 'not teens'
  END
FROM sys.people;

COALESCE

Returns the first argument that is not NULL or NULL if all arguments are NULL. Arguments must be the same type, and the returned 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. For example, when using TIMESTAMP and DATE data types, all values are cast to a timestamp. Syntax

SQL

COALESCE(value1, value2 [,...])

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

SELECT COALESCE(null,null,13,null,8);

Output: 13

COMMIT

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

SQL

COMMIT()

Example

SQL

SELECT COMMIT();

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

CURRENT_NODE()

Example

SQL

SELECT CURRENT_NODE();

Output: sql0

CURRENT_GROUPS

Returns the fully qualified names of groups to which the current user belongs. You can query the sys.groups system catalog table to view the group information. Syntax

SQL

CURRENT_GROUPS()

Example

SQL

SELECT CURRENT_GROUPS();

Output: ['sso_users@system', 'analysts@system']

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

CURRENT_NODE_ID()

Example

SQL

SELECT CURRENT_NODE_ID();

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

CURRENT_SCHEMA

Returns the name of the current schema. Syntax

SQL

CURRENT_SCHEMA()

Example

SQL

SELECT CURRENT_SCHEMA();

Output: schema@database

CURRENT_SESSION_ID

Returns the identifier of the current session as a Universally Unique IDentifier (UUID) that matches the id column in the sys.sessions system catalog table. Syntax

SQL

CURRENT_SESSION_ID()

Example

SQL

SELECT CURRENT_SESSION_ID();

Output: 6ffe8383-e894-44c0-91c5-38d5925f51ec

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 Ocient System initializes the name of the system as a random identifier, the UUID.

Syntax

SQL

CURRENT_SYSTEM()

Example

SQL

SELECT CURRENT_SYSTEM();

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

CURRENT_USER

Returns the user for the current connection. Syntax

SQL

CURRENT_USER()

Example

SQL

SELECT CURRENT_USER();

Output: user@database

DATABASE

Alias for CURRENT_DATABASE. Returns the name of the database to which the client is currently connected. Syntax

SQL

DATABASE()

Example

SQL

SELECT DATABASE();

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

GREATEST(value [,...])

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

SELECT GREATEST(12,13,5,8);

Output: 13

IF_NULL

Returns the first argument that is not NULL or NULL if all arguments are NULL. Arguments must be the same type, and the returned 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. For example, when using TIMESTAMP and DATE data types, all values are cast to a timestamp. Syntax

SQL

IF_NULL(value1, value2)

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

SELECT IF_NULL(null, 5)

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

LEAST(value [,...])

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

SELECT LEAST(12,13,5,8);

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

MURMUR3(value)

Argument	Data Type	Description
`value`	All SQL data types are supported.	Specified value for the MurmurHash3 hash function.

Example

SQL

SELECT MURMUR3(null);

Output: 0 Example

SQL

SELECT MURMUR3('fred');

Output: 331477181

NULL_IF

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

SQL

NULL_IF(value1, value2)

Example

SQL

SELECT NULL_IF(13,12);

Output: 13 Example

SQL

SELECT NULL_IF(13,13);

Output: NULL

RAND_UUID

Alias for UUID_GENERATE.

SHOW

The SHOW function lets you explore the database and its metadata for user-defined items. For details, see Information Schema. Syntax

SQL

SHOW <item>;

-- <item> can be any of the following:
{   COLUMNS,
    DATA_TYPES,
    DATABASES,
    GROUPS,
    INDEXES,
    RESERVED_WORDS,
    SCHEMATA,
    TABLES,
    USERS,
    VIEWS }

Examples This example uses SHOW to explore databases on the system.

SQL

SHOW DATABASES;

Output

SQL

database_name
created
--------------------------------------------------------------------------
test
2022-12-19 19:52:34.576426348

This example explores table data.

SQL

SHOW TABLES;

Output

SQL

table_catalog
table_schema
table_name
table_type
is_insertable_into
created_at
--------------------------------------------------------------------------
test
test_schema
test_table
BASE TABLE
YES
2022-12-20 14:45:13.858144104

UUID_GENERATE

Alias for RAND_UUID. Generates a random UUID value (version 4). For details about the UUID data type, see Data Types. Syntax

SQL

UUID_GENERATE()

Example Generate one UUID value.

SQL

SELECT UUID_GENERATE();

Output: 3f6b2b58-4c9a-4a32-9b6a-b2f9b8e2a41e

TYPE_STRIP

Returns the SQL type of the specified value. Syntax

SQL

TYPE_STRIP(value)

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

SELECT TYPE_STRIP(_R{1,2,3});

Output: MATRIX[1][3]

SQL

SELECT TYPE_STRIP(current_user());

Output: CHAR

SQL

SELECT TYPE_STRIP(NULL);

Output: NULL

SQL

SELECT TYPE_STRIP(NULL::INT[]);

Output: INT[]

VERSION

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

SQL

VERSION()

Example

SQL

SELECT VERSION();

Output: 22.0.0-20230105.2353

ZN

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

SQL

ZN(x)

Example

SQL

SELECT ZN(null);

Output: 0 Example

SQL

SELECT ZN(12);

Output: 12 Databases System Catalog Database Administration

​CASE

​COALESCE

​COMMIT

​CURRENT_DATABASE

​CURRENT_NODE

​CURRENT_GROUPS

​CURRENT_NODE_ID

​CURRENT_SCHEMA

​CURRENT_SESSION_ID

​CURRENT_SYSTEM

​CURRENT_USER

​DATABASE

​GREATEST

​IF_NULL

​LEAST

​MURMUR3

​NULL_IF

​RAND_UUID

​SHOW

​UUID_GENERATE

​TYPE_STRIP

​VERSION

​ZN

​Related Links

CASE

COALESCE

COMMIT

CURRENT_DATABASE

CURRENT_NODE

CURRENT_GROUPS

CURRENT_NODE_ID

CURRENT_SCHEMA

CURRENT_SESSION_ID

CURRENT_SYSTEM

CURRENT_USER

DATABASE

GREATEST

IF_NULL

LEAST

MURMUR3

NULL_IF

RAND_UUID

SHOW

UUID_GENERATE

TYPE_STRIP

VERSION

ZN

Related Links