Adding a transformation to a mapping

Your mapping model can include expressions that change the value or the data type of a source column to be compatible with a target column.
Prerequisite:
  • Creating a mapping model
  • Create mappings between a source and a target, or run the discover function and accept at least one mapping.

To add a transformation:

  1. Right-click a mapping model to open the mapping editor.
  2. Open the Expression Builder
    Location Actions
    From the mapping editor Right click on a mapping line and select Transform > Add to add a transformation from one or more source columns to a target column.
    From the properties view Right click on a mapping line and select Properties. You can create the transformation in the text field in the properties view or click the Expression Builder to open the Expression Builder.
  3. In the Expression Builder, double-click a child element from the Columns field. The fully qualified column name appears in the Transformation expression field.
  4. Double-click a function from the Functions field. The function appears in the Transformation expression field. You can manipulate the function or the column name to create a valid statement. If a part of the statement is not valid, it is underlined with a red wavy line if you enabled expression validation in the Workbench preferences.
  5. Click OK.
For example, from the mappings that are created in Figure 1, you can see that the matching elements do not actually match in data type. You need to transform the source element (WORKDEPT) so that it is compatible with the target element (DEPTNUMB):
Figure 1. Adding a transformationThe graphic displays the Expression Builder when adding a transformation to the source side.
Here is an example of the SQL script that you can generate from the mapping model after adding the transformation:
SELECT ASCII(S0.WORKDEPT) AS  DEPTNUMB,
        ...
FROM  SAMP.EMPLOYEE S0;

Feedback