# 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 is `true`

• `bValue` - value to return if Boolean is `false`

### 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 `false` and any other value is considered `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 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 [,...])``

### 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 for `NULL`

• `aValue` - the value to return if testVale is not `NULL`

• `bValue` - the value to return if testVale is `NULL`

### 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'))``