Designing a Transformation in Query Builder

Certain Transformations are easier to create with a graphical tool that can visualize the necessary tables/views/columns. InfoNaut is a query tool that ships with CONNX and has a graphical component that enables you to visualize transformations.

To use the Query Builder, Go to the CONNX Driver section of the Windows Start menu and select InfoNaut. Establish a DSN-less connection to the CDD that was uploaded to DataSync and select Build Query from the tool bar.

The QueryBuilder screen appears as follows:

The right part of the main Query Builder window lists the tables that can be dragged to the design pane. The bottom part is a FreeForm area for SQL.

In this example, the two tables (CUSTOMERS_RMS and ORDERS_RMS) will be used to generate a transform that produces a combined table that produces output only if the order occurred in 1997.

Query Tool allows you to select only the fields you're interested in from the two tables. You can create a join arrow by clicking the left mouse button. The bottom of the design pane will show the selected fields.

The Generated SQL does not include the condition where the year must be 1997. The SQL to generate this is YEAR(CISAM.mike.Orders_cisam.ORDERDATE) = '1997'. This can be added in the grid below.

The WHERE clause with its criteria is added.

More complex expressions can be created using the Expression Builder.

Click Done - the SQL Query will be copied to the Free-Form area of the Transformation.