The SQL transformation executes a dynamic SQL query that inserts the unit price and package price data into the Prod_Cost table. The SQL transformation receives the column names to update in the UnitPrice_Query and PkgPrice_Query ports.
When you create an SQL transformation, you define the transformation mode, the database type, and the type of connection. You cannot change the mode or connection type after you create the transformation.
Create an SQL transformation with the following properties:
Query Mode.
The SQL transformation executes dynamic SQL queries.
Static Connection.
The SQL transformation connects once to the database with the connection object you define in the Workflow Manager.
The following figure shows the SQL transformation Ports tab with its SQL Query and Query Description:
The SQL transformation has a dynamic SQL query that updates one of the UnitPrice columns and one of the PkgPrice columns in the Prod_Cost table based on the column names it receives in the UnitPrice_Query and the PkgPrice_Query ports.
The SQL transformation has the following query:
Update Prod_Cost set ~UnitPrice_Query~= ?UnitPrice?, ~PkgPrice_Query~ = ?PkgPrice? where ProductId = ?ProductId?;
The SQL transformation substitutes the UnitPrice_Query and PkgPrice_Query string variables with the column names to update.
The SQL transformation binds the ProductId, UnitPrice and PkgPrice parameters in the query with data that it receives in the corresponding ports.
For example, the following source row contains a unit price and a package price for product 100:
100,M,100,110
When the PriceCode is “M,” the prices are manufacturing prices. The Expression transformation passes MUnitprice and MPkgPrice column names to the SQL transformation to update.
The SQL transformation executes the following query:
Update Prod_Cost set MUnitprice = 100, MPkgPrice = 110 where ProductId = ‘100’;
The following source row contains wholesale prices for product 100:
100,W,120,200
The Expression transformation passes WUnitprice and WPkgPrice column names to the SQL transformation. The SQL transformation executes the following query:
Update Prod_Cost set WUnitprice = 120, WPkgPrice = 200 where ProductId = ‘100’;
ASK INFAPreview
