Null handling functions
You can use the null handling functions in the Transformer stage to handle nulls in derivations.
If you use input columns in an output column expression, a null value in any input column causes a null to be written to the output column. You can, however, use the null handling functions to handle nulls explicitly.
The following functions are available in the Null Handling category. Square brackets indicate an argument is optional. The examples show the function as it appears in a Derivation field in the Transformer stage.
- IsNotNull
- Returns true when an expression does not evaluate to the null
value.
- Input: any
- Output: true/false (int8)
- Examples. If the Derivation field for an output column
contained the following code, then the Transformer stage checks if
the input column named mylink.mycolumn contains a null value. If the
input column does not contain a null, the output column contains the
value of the input column. If the input column does contain a null,
then the output column contains the string NULL.
If IsNotNull(mylink.mycolumn) Then mylink.mycolumn Else "NULL"
- IsNull
- Returns true when an expression evaluates to the null value.
- Input: any
- Output: true/false (int8)
- Examples. If the Derivation field for an output column
contained the following code, then the Transformer stage checks if
the input column named mylink.mycolumn contains a null value. If the
input column contains a null, the output column contains the string
NULL. If the input column does not contain a null, then the output
column contains the value of the input column.
If IsNull(mylink.mycolumn) Then "NULL" Else mylink.mycolumn
- NullToEmpty
- Returns an empty string if the input column is null, otherwise
returns the input column value.
- Input: input column
- Output: input column value or empty string
- Examples. If the Derivation field for an output column
contained the following code, then the Transformer stage checks if
the input column named mylink.mycolumn contains a null value. If the
input column contains a null, the output column contains an empty
string. If the input column does contain a null, then the output column
contains the value from the input column.
NullToEmpty(mylink.mycolumn)
- NullToZero
- Returns zero if the input column is null, otherwise returns the
input column value.
- Input: input column
- Output: input column value or zero
- Examples. If the Derivation field for an output column contained the following code, then
the Transformer stage checks if the input column named mylink.mycolumn contains a null value. If the
input column contains a null, the output column contains zero. If the input column does not contain
a null, then the output column contains the value from the input
column.
NullToZero(mylink.mycolumn)
- NullToValue
- Returns the specified value if the input column is null, otherwise
returns the input column value.
- Input: input column, value
- Output: input column value or value
- Examples. If the Derivation field for an output column
contained the following code, then the Transformer stage checks if
the input column named mylink.mycolumn contains a null value. If the
input column contains a null, the output column contains 42. If the
input column does contain a null, then the output column contains
the value from the input column.
NullToValue(mylink.mycolumn,42)
- SetNull
- Assigns a null value to the target column.
- Input: -
- Output: -
- Examples. If the Derivation field for an output column
contained the following code, then the Transformer stage sets the
output column to null:
setnull()