NULL handling functions in Datastage

Reading Time: < 1 minute
If IsNotNull(mylink.mycolumn) Then mylink.mycolumn Else “NULL” 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 IsNull(mylink.mycolumn) Then “NULL” Else mylink.mycolumn 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
NullToEmpty(mylink.mycolumn)Returns an empty string if the input column is null, otherwise returns the input column value
NullToZero(mylink.mycolumn)Returns zero if the input column is null, otherwise returns the input column value
NullToValue(mylink.mycolumn,42)Returns the specified value if the input column is null, otherwise returns the input column value
setnull()Assigns a null value to the target column

Leave a Reply