> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Other Functions and Expressions

export const Ocient = "Ocient®";

The {Ocient} 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 SQL theme={null}
--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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
COALESCE(value1, value2 [,...])
```

| **Argument**   | **Data Type**                                                            | **Description**                                           |
| -------------- | ------------------------------------------------------------------------ | --------------------------------------------------------- |
| `value [,...]` | All data types are supported. <br />All values must be of the same type. | Values for comparison to find the value that is not NULL. |

**Example**

```sql SQL theme={null}
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 SQL theme={null}
COMMIT()
```

**Example**

```sql SQL theme={null}
SELECT COMMIT();
```

Output: `cd0d53e3aa`

### CURRENT\_DATABASE

Alias for [DATABASE](#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 SQL theme={null}
CURRENT_NODE()
```

**Example**

```sql SQL theme={null}
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 SQL theme={null}
CURRENT_GROUPS()
```

**Example**

```sql SQL theme={null}
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 SQL theme={null}
CURRENT_NODE_ID()
```

**Example**

```sql SQL theme={null}
SELECT CURRENT_NODE_ID();
```

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

### CURRENT\_SCHEMA

Returns the name of the current schema.

**Syntax**

```sql SQL theme={null}
CURRENT_SCHEMA()
```

**Example**

```sql SQL theme={null}
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 SQL theme={null}
CURRENT_SESSION_ID()
```

**Example**

```sql SQL theme={null}
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](/cluster-and-node-management#alter-system-rename-to) SQL statement.

<Info>
  If you never set the system name, the Ocient System initializes the name of the system as a random identifier, the UUID.
</Info>

**Syntax**

```sql SQL theme={null}
CURRENT_SYSTEM()
```

**Example**

```sql SQL theme={null}
SELECT CURRENT_SYSTEM();
```

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

### CURRENT\_USER

Returns the user for the current connection.

**Syntax**

```sql SQL theme={null}
CURRENT_USER()
```

**Example**

```sql SQL theme={null}
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 SQL theme={null}
DATABASE()
```

**Example**

```sql SQL theme={null}
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 SQL theme={null}
GREATEST(value [,...])
```

| **Argument**   | **Data Type**                                                                                                     | **Description**                                                     |
| -------------- | ----------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------- |
| `value [,...]` | All data types are supported. <br /><br />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 SQL theme={null}
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 SQL theme={null}
IF_NULL(value1, value2)
```

| **Argument**   | **Data Type**                                                            | **Description**                                           |
| -------------- | ------------------------------------------------------------------------ | --------------------------------------------------------- |
| `value [,...]` | All data types are supported. <br />All values must be of the same type. | Values for comparison to find the value that is not NULL. |

**Example**

```sql SQL theme={null}
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 SQL theme={null}
LEAST(value [,...])
```

| **Argument**   | **Data Type**                                                                                                     | **Description**                                                      |
| -------------- | ----------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------- |
| `value [,...]` | All data types are supported. <br /><br />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 SQL theme={null}
SELECT LEAST(12,13,5,8);
```

Output: `5`

### MURMUR3

Returns a 32-bit [MurmurHash3 hash](https://github.com/aappleby/smhasher/blob/master/README.md) 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 SQL theme={null}
MURMUR3(value)
```

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

**Example**

```sql SQL theme={null}
SELECT MURMUR3(null);
```

Output: `0`

**Example**

```sql SQL theme={null}
SELECT MURMUR3('fred');
```

Output: `331477181`

### **NULL\_IF**

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

**Syntax**

```sql SQL theme={null}
NULL_IF(value1, value2)
```

**Example**

```sql SQL theme={null}
SELECT NULL_IF(13,12);
```

Output: `13`

**Example**

```sql SQL theme={null}
SELECT NULL_IF(13,13);
```

Output: `NULL`

### RAND\_UUID

Alias for [UUID\_GENERATE](#uuid_generate).

### SHOW

The `SHOW` function lets you explore the database and its metadata for user-defined items. For details, see [Information Schema](/information-schema).

**Syntax**

```sql SQL theme={null}
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 SQL theme={null}
SHOW DATABASES;
```

*Output*

```sql SQL theme={null}
database_name
created
--------------------------------------------------------------------------
test
2022-12-19 19:52:34.576426348
```

This example explores table data.

```sql SQL theme={null}
SHOW TABLES;
```

*Output*

```sql SQL theme={null}
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](#rand_uuid).

Generates a random UUID value (version 4).

For details about the UUID data type, see [Data Types](/data-types).

**Syntax**

```sql SQL theme={null}
UUID_GENERATE()
```

**Example**

Generate one UUID value.

```sql SQL theme={null}
SELECT UUID_GENERATE();
```

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

### TYPE\_STRIP

Returns the SQL type of the specified value.

**Syntax**

```sql SQL theme={null}
TYPE_STRIP(value)
```

| **Argument** | **Data Type**                 | **Description**                                                                                              |
| ------------ | ----------------------------- | ------------------------------------------------------------------------------------------------------------ |
| `value`      | All data types are supported. | Specified value for the determination of the type. <br />You can specify columns, literals, and expressions. |

**Examples**

```sql SQL theme={null}
SELECT TYPE_STRIP(_R{1,2,3});
```

Output: `MATRIX[1][3]`

```sql SQL theme={null}
SELECT TYPE_STRIP(current_user());
```

Output: `CHAR`

```sql SQL theme={null}
SELECT TYPE_STRIP(NULL);
```

Output: `NULL`

```sql SQL theme={null}
SELECT TYPE_STRIP(NULL::INT[]);
```

Output: `INT[]`

### VERSION

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

**Syntax**

```sql SQL theme={null}
VERSION()
```

**Example**

```sql SQL theme={null}
SELECT VERSION();
```

Output: `22.0.0-20230105.2353`

### ZN

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

**Syntax**

```sql SQL theme={null}
ZN(x)
```

**Example**

```sql SQL theme={null}
SELECT ZN(null);
```

Output: `0`

**Example**

```sql SQL theme={null}
SELECT ZN(12);
```

Output: `12`

## Related Links

[Databases](/databases)

[System Catalog](/system-catalog)

[Database Administration](/database-administration)