The documentation portal is undergoing maintenance. During this period, you can't create any new user credentials. If you do, you'll need to recreate the user credential after the maintenance period ends.
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.