This example shows how to configure an Expression transformation and an SQL transformation to generate SQL queries based on the value of a column in a source file.
In this example, you have a database table that contains product prices. You need to update the prices from a transaction file. Each transaction row updates the wholesale, retail, or manufacturing prices in the database based on a price code column.
The source file is a flat file. You can configure an Expression transformation to return the column names to update based on the value of a price code column in each source row. The Expression transformation passes the column names to the SQL transformation. The SQL transformation runs a dynamic SQL query that updates columns in the Prod_Cost table based on the column names it receives. The SQL transformation returns database errors to the Error_File target.
The following figure shows the how the Expression transformation passes column names to the SQL transformation:
The mapping contains the following components:
PPrices source definition.
The PPrices flat file contains a product ID, package price, unit price, and price code. The price code defines whether the package price and unit price are wholesale, retail, or manufactured prices.
Error_File flat file target definition
. The target contains the Datastring field that receives database errors from the SQL transformation.
Exp_Dynamic_Expression transformation.
The Expression transformation defines which Prod_Cost column names to update based on the value of the PriceCode column. It returns the column names in the UnitPrice_Query and PkgPrice_Query ports.
SQL_Dynamic_Query transformation
. The SQL transformation has a dynamic SQL query to update a UnitPrice column and a PkgPrice column in the Prod_Cost table. It updates the columns named in the UnitPrice_Query and PkgPrice_Query columns.
The mapping does not contain a relational table definition for the Prod_Cost table
.
The SQL transformation has a static connection to the database that contains the Prod_Cost table. The transformation generates the SQL statements to update the unit prices and package prices in the table.
ASK INFAPreview
