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()
```