Expression builder

The expression builder helps you create conditions or transformations on mappings. The expressions are SQL fragments that appear in the generated SQL statement. The output of an expression can be used either to transform a value from a source to a target or to define a condition for a filter or a join. A transformation can always be used from source to targets, but it is necessary as soon as there is more then one source

You can open the expression builder from the Properties view of a mapping model, or from the context menu of a mapping or a mapping group. You can type a condition or transformation statement in the expression text field in the properties view, or select from the menus and fields in the mapping editor view. Whether you create a condition or transformation depends on the type of refinement. Joins and filters are conditions while a transformation changes data. The result of a condition must be a boolean, but the result of a transformation must be a value whose type fits the type of the target object.

The expressions that you create are saved to the current mapping model. The expression builder includes SQL functions in the list of available functions. These SQL functions are a set of standard functions along with User Defined Functions from the target .dbm You can generate SQL statements that include expressions or transformations from the mapping model.

By using the expression builder, you can perform the following actions:

Structure of the expression builder

The expression builder consists of a list of available fields or columns, a list of available functions and User Defined Functions (depending on the target schema), and an edit pane that displays the expression with its object identifiers. An object identifier contains at least a schema name, a table name and a column name. If there is more then one database in the mapping, the database name is added to the identifier. The edit pane in the expression builder can contain multiple lines with carriage returns.

The content of the fields or columns in the expression builder reflects the source columns that are valid part for the current expression.

Content assist function

The expression builder uses a standard Eclipse content assist function. The content assist function provides a list of valid functions that are applicable according to the current context of the mapped values. Press the shortcut keys CTRL+SPACE to open the content assist window.

The edit pane in the expression builder has other menu options that contain shortcut keys:
Table 1. Menu options in the Expression Builder
Action Shortcut keys
Copy CTRL+C
Cut CTRL+X
Paste CTRL+V
Undo CTRL+Y
Redo CTRL+Z
Select all CTRL+A

Validating expressions

The edit pane displays phrases that are not valid with red wavy underscores if the Validate expressions check box in the Preferences page is enabled. The expression builder verifies that only valid columns are contained in the expression. The expression builder verifies that the fully qualified column names are correct. The expression builder validates the syntax of the expression and the identifiers that are used within the expression. The validation is appropriate if the mapping does not span multiple databases on the source side.

Mapping specification language model example

When you complete the expression, condition, or transformation, the resulting expression appears as part of the .msl model. In the .msl model, the expression is represented by variable names.

The following examples show some valid expressions that can be used in the mapping model:
A simple filter on records of a specific department:
COMPANY.DEPARTMENT.DNUMBER = 10

A simple join between two tables:
COMPANY.DEPARTMENT.ID = COMPANY.EMPLOYEE.DEPID

A simple transformation that calculates a different target price:
COMPANY.PRODUCT.PRICE - COMPANY.PRODUCT.DISCOUNT

Feedback