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'))© 2021 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.