How to enter:
The Expression builder can be opened from either the Query builder or the Column Mapping tab in the Transform Properties wizard. From the Column Mapping tab, double click on the Source column that you wish to build into an expression. Another way to enter the Expression builder is to right click on the Column map table and select Expression Builder from the context menu. This will open the Expression builder and put the selected column name into the Expression field. From the Query builder, either right click on the Expression field and select Expression Builder from the menu. Another way is to select the box with the "..." at the right side of the expression cell in the query builder.
Enter the Expression builder
from the Column Map
Enter the Expression builder from the Query Builder.
Why to use:
The Expression builder is designed to help the user create complex expressions easily. It lists functions supported by CONNX, gives information about the functions and the arguments it contains. This can be a helpful reminder of standard SQL functions and a good way to learn about CONNX specific functions.
The Layout:
The Expression builder is composed of the following parts: Functions available list, Columns available list, Function Name and Description, Function Argument Description table, Expression Alias input field, and Expression SQL field. All functions supported by CONNX are listed in groups in the left panel, the groups can be expanded or collapsed to see all functions available. When a function is selected the name and the description of the function are shown in the table on the top right. Also after a function is selected the list of function arguments and their descriptions can be found in the Argument Description table. The Expression field at the lower right can either be edited directly or it will show the built Expression. The Expression Alias entry field is for creating a simple name for the expression that can be used for the Target column name when mapping.
Example:
The following example goes step-by-step through an example of building a transform
Upon entry to the Expression Builder the contents
of the column selected will appear in the Expression
SQL field and be highlighted.
Browse through the function list expanding or collapsing
groups to find the appropriate function. Select functions that
may be of interest to see their description to aid selection. Once
the appropriate function is found double click or select the Add Function button and it will
appear in the Expression SQL
field.
When the function appears
in the Expression SQL field
the highlighted text will then appear as the first argument of the
expression. Other expression arguments can be filled out by
either direct editing in the Expression
SQL field, or by editing the value cell in the Expression
Arguments table.
The Expression
Arguments table can be used to enter the values for the expression.
The first field can be edited and used to put values into the
function. The second column in the table tells the datatype
of argument the function is expecting. The third column has
a description of what the purpose of the argument is in the function.
In the for each argument in the table edit the Value field
and enter the appropriate column name, characters or numbers for the
function argument. Note, as values are entered here they also
appear in the Expression SQL
field.
Another way to enter the values for the function arguments is to edit them directly in the Expression SQL field. Any changes here will be reflected in the Expression Arguments table.
Add another function to
the expression by placing the cursor to the desired location of the
new function in the Expression SQL
field and double clicking the function in the list. Dragging
the cursor to make a selection in the Expression
SQL field before double clicking the function will cause the
selected text to appear in the first argument of the function.
Add an alias to be used for the corresponding target table column name. In the Expression alias field type a simple and descriptive name for the expression. It will automatically be added to the end of the expression.