How to enter:
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. For more information on starting the Query Builder, see the section on Designing a Transformation in 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.
