Conditional and Null Handling Functions

Conditional functions are functions that return a value depending on the values for other variable.  Null Handling functions are specialized functions for dealing with NULL values in the data.

Function Usage and description


Conditional Expressions

Usage: CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

The first version above returns the result where value=compare_value.

The second version returns the result for the first condition that is true.

If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.

The default return type of a CASE expression is the compatible aggregated type of all return values, but also depends on the context in which it is used.

If used in a string context, the result is returned as a string. If used in a numeric context, then the result is returned as a decimal, real, or integer value.

Decode Values

Usage: DECODE(expression, search1, replace1, search2, replace2, ..., default)

This function checks whether expression is equal to the provided search1 value, and if so, returns replace1. If not, it continues with the search2 value, and so on.

If no search value is matched, it returns the provided default value, or NULL if no default value is provided in the last parameter.

Expression can be any data type.

Null If

Usage: NULLIF(expr1,expr2)

Returns NULL if expr1 = expr2 is true, otherwise returns expr1.

This is equivalent to specifying CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

Null Coalescing

Usage: COALESCE(value1,...)

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

Null Comparison

Usage: NVL2(expression, notnull return value, null return value)

If expression is not null, the notnull return value is returned. If it is null, the null return value is returned.

Null Check

Usage: expression IS NULL; expression IS NOT NULL

Test whether a variable or expression is NULL (or is NOT NULL). Returns a 1 or 0 (TRUE or FALSE)

Creating Null values of specific types

Usage: null_as_decimal(), null_as_int(), null_as_date(), null_as_datetime(), null_as_string()

These functions will return a null value of the given type.  In most cases, you can simply use the code *null* (no asterisks) to represent a null value.  However, especially within CASE/WHEN statements, it is helpful to  specify clearly the desired type of a null value in order to ensure that LityxIQ can determine the overall type of the created variable.