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:
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.
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.
Action | Shortcut keys |
---|---|
Copy | CTRL+C |
Cut | CTRL+X |
Paste | CTRL+V |
Undo | CTRL+Y |
Redo | CTRL+Z |
Select all | CTRL+A |
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.
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.
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