Table of Contents

Search

  1. Preface
  2. Working with Transformations
  3. Aggregator Transformation
  4. Custom Transformation
  5. Custom Transformation Functions
  6. Data Masking Transformation
  7. Data Masking Examples
  8. Expression Transformation
  9. External Procedure Transformation
  10. Filter Transformation
  11. HTTP Transformation
  12. Identity Resolution Transformation
  13. Java Transformation
  14. Java Transformation API Reference
  15. Java Expressions
  16. Java Transformation Example
  17. Joiner Transformation
  18. Lookup Transformation
  19. Lookup Caches
  20. Dynamic Lookup Cache
  21. Normalizer Transformation
  22. Rank Transformation
  23. Router Transformation
  24. Sequence Generator Transformation
  25. Sorter Transformation
  26. Source Qualifier Transformation
  27. SQL Transformation
  28. Using the SQL Transformation in a Mapping
  29. Stored Procedure Transformation
  30. Transaction Control Transformation
  31. Union Transformation
  32. Unstructured Data Transformation
  33. Update Strategy Transformation
  34. XML Transformations

Transformation Guide

Transformation Guide

Defining the SQL Transformation

Defining the SQL Transformation

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 Ports tab of the Edit Transformations dialog box contains the port name, datatype, native type, precision, and scale columns. The tab also contains the transformation name, transformation type, default value, description, SQL query and query description fields.
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’;

0 COMMENTS

We’d like to hear from you!