The Expression Builder
The Expression Builder can be opened from the Query Builder in the InfoNaut tool which can be found in the Windows Start | CONNX Driver menu. To start the Expression Builder, connect to the CDD being used for DataSync with InfoNaut and click the Query Builder button. Right click on the Expression field and select Expression Builder from the menu. Another way to enter the Expression Builder is to select the box with the "..." at the right side of the expression cell in the query builder.
Functions
The Expression builder is designed to help the user create complex expressions easily. It lists functions supported by CONNX and 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.
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
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 available functions. When a function is selected, the name and the description of the function are shown in the table on the top right. In addition, 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.
How to build a transformation with the expression builder
When you open the Expression Builder window, the contents of the selected column will be highlited and appear in the Expression SQL field.
1. Browse through the function list 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 it. (you can also select the Add Function button) The function will appear in the Expression SQL field.
2. Fill additional expression arguments if necessary.
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.
3. 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. All values that you enter here 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.
4. 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.
5. Add an alias for the corresponding target table column name. Type a simple and descriptive name for the expression in the Expression alias field. It will automatically be added to the end of the expression.