System Functions
CASEWHEN
Description
Returns 'aValue' if the Boolean expression is true, 'bValue' otherwise.
CASEWHEN (boolean , aValue , bValue)Parameters
-
boolean- the value to evaluate -
aValue- the value to return if Boolean istrue -
bValue- value to return if Boolean isfalse
Example
Evaluate the ID and return the corresponding value:
CASEWHEN(ID=1, 'A', 'B')CAST
Description
Converts a value to another data type using one the following conversion rules:
-
When converting a number to a Boolean,
0 is considered `falseand any other value is consideredtrue -
When converting a Boolean to a number,
falseis0andtrueis1 -
When converting a number to a number of another type, the value is checked for overflow
-
When converting a number to a binary type, the number of bytes matches the precision
-
When converting a string to a binary type, it is hex-encoded
-
A hex string can be converted to the binary type and then to a number; if a direct conversion is not possible, the value is first converted to a string
CAST (value AS dataType)Parameters
-
value- the value to convert -
dataType- the data type to convert the value to
Examples
CAST(NAME AS INT);
CAST(65535 AS BINARY);
CAST(CAST('FFFF' AS BINARY) AS INT);COALESCE | NVL
Description
Returns the first value that is not NULL.
{COALESCE | NVL } (aValue, bValue [,...])Parameters
aValue, bValue - values to process
Example
Return the first non-null value out of A, B, and C:
COALESCE(A, B, C)CONVERT
Description
Converts a value to another data type.
CONVERT (value , dataType)Parameters
-
value- the value to convert -
dataType- the data type to convert the value to
Example
Convert NAME to an integer:
CONVERT(NAME, INT)DECODE
Description
Returns the first matching value. NULL matches NULL`. If no match is found, NULL or the last parameter is returned (if the parameter count is even).
DECODE(value, whenValue, thenValue [,...])Parameters
-
value- the value to match against -
whenValue,thenValue, etc. - values to check
Example
Return the first value that matches random number greater than 0.5:
DECODE(RAND()>0.5, 0, 'Red', 1, 'Black')GREATEST
Description
Returns the greatest value that is not NULL, or NULL if all values are NULL.
GREATEST(aValue, bValue [,...])Parameters
aValue, bValue - values to process
Example
Return the greatest values out of 1, 2, and 3:
GREATEST(1, 2, 3)IFNULL
Description
Returns 'a' if the value is not NULL, 'b' otherwise.
IFNULL(aValue, bValue)Parameters
aValue, bValue - values to process
Example
Check NULL and space ('') for being NULL:
IFNULL(NULL, '')LEAST
Description
Returns the smallest value that is not NULL, or NULL if all values are NULL.
LEAST(aValue, bValue [,...])Parameters
Example
LEAST(1, 2, 3)NULLIF
Description
Returns NULL if 'a' equals 'b', otherwise returns 'a'.
NULLIF(aValue, bValue)Parameters
aValue, bValue - values to process
Example
Check if A=B:
NULLIF(A, B)NVL2
Description
If the test value is NULL, returns 'bValue'. Otherwise, returns 'aValue'. The data type of the returned value is the same as that of 'aValue'.
NVL2(testValue, aValue, bValue)Parameters
-
testValue- the value to test forNULL -
aValue- the value to return if testVale is notNULL -
bValue- the value to return if testVale isNULL
Example
Test if X is `NULL`:NVL2(X, 'not null', 'null')
TABLE | TABLE_DISTINCT
Description
Returns the result set that matches the criteria. TABLE_DISTINCT removes duplicate rows.
TABLE | TABLE_DISTINCT (name dataType = expression)Parameters
-
name- the table name -
dataType- the data type to return -
expression- teh expression that defines the dataType
Example
Return everything from the table:
SELECT * FROM TABLE(ID INT=(1, 2), NAME VARCHAR=('Hello', 'World'))© 2024 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.