Open topic with navigation
Data extracted from databases can yield NULL values if the database contains NULL values and the null indicator is retained upon extraction via DMExpress. If the target column is set to NOT NULLABLE, or if you prefer to not have NULL values in your transformation and/or subsequent output(s), you can use named values that conditionally replace NULL values with a specified default value.
When you want to replace NULL database values with a specified default value in DMExpress, ensure that Retain null indicator is checked in the Source Database Table dialog for each nullable column:
Otherwise, the extracted value for NULL values will be undefined.
To replace the NULL values, you can create one or more named values (one for each column whose NULL values are to be replaced with a specified default value) with an expression of the following form:
IfThenElse(ColumnValue = NULL, DefaultValue, ColumnValue)
||is the database column being extracted|
||is a named value, number constant, text constant, etc.|
This expression translates to "If the value in this column is NULL, use the defined default value, otherwise use the value it already has." For example:
You would then use the named value(s) in place of the corresponding column name wherever you want the NULL values to be replaced, such as in the transformation, target database table mapping, target reformat, etc. For example, in the Reformat Target Layout dialog:
For more information, see Source Database Table dialog in the DMExpress Help.
Copyright © 2016 Syncsort All rights reserved.