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:
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.
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.
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.