GridGain Developers Hub
GitHub logo GridGain iso GridGain.com
GridGain Software Documentation

System Functions

COALESCE

{COALESCE | NVL } (aValue, bValue [,...])

Description

Returns the first value that is not null.

Examples

COALESCE(A, B, C)

DECODE

DECODE(value, whenValue, thenValue [,...])

Description

Returns the first matching value. NULL is considered to match NULL. If no match was found, then NULL or the last parameter (if the parameter count is even) is returned.

Examples

DECODE(RAND()>0.5, 0, 'Red', 1, 'Black')

GREATEST

GREATEST(aValue, bValue [,...])

Description

Returns the largest value that is not NULL, or NULL if all values are NULL.

Examples

GREATEST(1, 2, 3)

IFNULL

IFNULL(aValue, bValue)

Description

Returns the value of 'a' if it is not null, otherwise 'b'.

Examples

IFNULL(NULL, '')

LEAST

LEAST(aValue, bValue [,...])

Description

Returns the smallest value that is not NULL, or NULL if all values are NULL.

Examples

LEAST(1, 2, 3)

NULLIF

NULLIF(aValue, bValue)

Description

Returns NULL if 'a' is equals to 'b', otherwise 'a'.

Examples

NULLIF(A, B)

NVL2

NVL2(testValue, aValue, bValue)

Description

If the test value is null, then 'b' is returned. Otherwise, 'a' is returned. The data type of the returned value is the data type of 'a' if this is a text type.

Examples

NVL2(X, 'not null', 'null')

CASEWHEN

CASEWHEN (boolean , aValue , bValue)

Description

Returns 'aValue' if the boolean expression is true, otherwise 'bValue'.

Examples

CASEWHEN(ID=1, 'A', 'B')

CAST

CAST (value AS dataType)

Description

Converts a value to another data type. The following conversion rules are used:

  • When converting a number to a boolean, 0 is considered as false and every other value is true.

  • When converting a boolean to a number, false is 0 and true is 1.

  • When converting a number to a number of another type, the value is checked for overflow.

  • When converting a number to binary, the number of bytes will match the precision.

  • When converting a string to binary, it is hex encoded.

  • A hex string can be converted into the binary form and then to a number. If a direct conversion is not possible, the value is first converted to a string.

Examples

CAST(NAME AS INT);
CAST(65535 AS BINARY);
CAST(CAST('FFFF' AS BINARY) AS INT);

CONVERT

CONVERT (value , dataType)

Description

Converts a value to another data type.

Examples

CONVERT(NAME, INT)

TABLE

TABLE	| TABLE_DISTINCT	(name dataType = expression)

Description

Returns the result set. TABLE_DISTINCT removes duplicate rows.

Examples

SELECT * FROM TABLE(ID INT=(1, 2), NAME VARCHAR=('Hello', 'World'))