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.

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 contain a null, then the output column contains the value from the input column.

     NullToZeroy(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: