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 |
|
CASE/WHEN 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. |