Table of Contents

Search

  1. Preface
  2. Transformations
  3. Source transformation
  4. Target transformation
  5. Aggregator transformation
  6. Cleanse transformation
  7. Data Masking transformation
  8. Deduplicate transformation
  9. Expression transformation
  10. Filter transformation
  11. Hierarchy Builder transformation
  12. Hierarchy Parser transformation
  13. Hierarchy Processor transformation
  14. Input transformation
  15. Java transformation
  16. Java transformation API reference
  17. Joiner transformation
  18. Labeler transformation
  19. Lookup transformation
  20. Mapplet transformation
  21. Normalizer transformation
  22. Output transformation
  23. Parse transformation
  24. Python transformation
  25. Rank transformation
  26. Router transformation
  27. Rule Specification transformation
  28. Sequence Generator transformation
  29. Sorter transformation
  30. SQL transformation
  31. Structure Parser transformation
  32. Transaction Control transformation
  33. Union transformation
  34. Velocity transformation
  35. Verifier transformation
  36. Web Services transformation

Transformations

Transformations

Unconnected SQL transformation example

Unconnected SQL transformation example

You are updating employee salaries with a cost of living increase. You have a CSV file that contains employee names and IDs. You need to add each employee's salary, calculate their increase, and write the data to a new CSV file.
You have the following source data:
EMP_ID, EMP_NAME 1001, John 1002, Alice 1003, Mary 1004, Mark 1005, Stephan
You have a stored procedure called ADD_SALARY in an Oracle database that adds the employee's salary to the file and a second stored procedure called SALARY_INCREASE which calculates the salary increase. In the mapping, use two unconnected SQL transformations to call the stored procedures and two Expression transformations to call the SQL transformations.
The following image shows the mapping:
The main data flow includes a Source transformation, two Expression transformations, and a Target transformation. The mapping also includes two SQL transformations that are not connected to the data flow.
Configure the transformations in the following ways:
Source transformation
Configure the Source transformation to load the source data that you want to use.
SQL_Add_Salary transformation
Configure the first SQL transformation to call the ADD_SALARY stored procedure.
On the
SQL
tab, select the connection that contains the ADD_SALARY stored procedure and then select the stored procedure.
Select
Unconnected stored procedure
.
The stored procedure has one input field for the employee ID and returns the current salary in the output field.
SQL_Increase transformation
Configure the second SQL transformation to call the SALARY_INCREASE stored procedure.
On the
SQL
tab, select the connection that contains the SALARY_INCREASE stored procedure and then select the stored procedure.
Select
Unconnected stored procedure
.
The stored procedure has one input field for the employee name and returns the new salary in the output field.
Expression_Add_Salary transformation
Configure the first Expression transformation to call the SQL_Add_Salary transformation. Create a variable field for the input parameter and an output field to capture the output of the stored procedure.
On the
Expression
tab, add a variable field named ID and configure its value as the EMP_ID field in the source. Create an output field called salary to capture the return value of the ADD_SALARY stored procedure in the first SQL transformation. Configure the salary field to call the ADD_SALARY stored procedure with the following expression:
:SP.SQL_Add_Salary(ID, PROC_RESULT)
The expression takes the variable field ID as the input parameter of the stored procedure and returns the salary value to the SALARY output field.
The following image shows how you configure the Expression transformation:
The image shows the Expression tab for the Expression_Add_Salary transformation. The transformation contains two field expressions.
Expression_Increase transformation
Configure the second Expression transformation to call the SQL_increase transformation. Add a variable field called CurrentSalary and configure its value as the incoming salary field. Add a output field called newSalary to capture the return value of the SALARY_INCREASE stored procedure. Configure the newSalary field to call the SALARY_INCREASE stored procedure with the following expression:
:SP.SQL_increase(CurrentSalary, PROC_RESULT)
The expression takes the variable field CurrentSalary as the input parameter of the stored procedure and returns the new salary to the newSalary output field.
The following image shows how you configure the Expression transformation:
The image shows the Expression tab for the Expression_Increase transformation. The transformation contains two field expressions.
Target transformation
Configure the Target transformation to create a target file at run time.
When you run the mapping, you get the following results:
EMP_ID, EMP_NAME, salary, newSalary 1001, John, 400, 480 1002, Alice, 500, 600 1003, Mary, 400, 480 1004, Mark, 700, 840 1005, Stephan, 600, 720