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

SQL transformation configuration

SQL transformation configuration

Use the
Properties
panel to configure the SQL transformation. When you configure the transformation, you specify the transformation name and description, configure fields and field mapping, and specify the type of SQL that the transformation processes. You also set the advanced properties for the transformation.
The following image shows the
Properties
panel of an SQL transformation that is configured to process a stored procedure:
On the Properties panel an Oracle connection is selected, the SQL type is set to "Stored Procedure," and a stored procedure named "SALARY_INCREMENT_DOUBLE" is selected from the database.
Configure the transformation using the following tabs on the
Properties
panel:
General
Configure the SQL transformation name and optional description.
Incoming Fields
Define field rules that determine the data to include in the transformation.
Not applicable to unconnected SQL transformations.
SQL
Define the database connection and the type of SQL that the transformation processes: either a stored procedure, stored function, or query.
If you configure the transformation to process a stored procedure, stored function, or saved query, you select the stored procedure, stored function, or saved query on this tab.
If you configure the transformation to process a stored procedure, you can choose to run the transformation as an unconnected transformation.
If you configure the transformation to process a user-entered query, the SQL editor appears on this tab. Enter the query in the SQL editor.
Input Fields
For transformations that process stored procedures, displays the stored procedure input fields.
Field Mapping
For stored procedure and stored functions, specify how to map incoming fields to the input fields of the selected stored procedure or function.
You do not configure the field mapping for queries or unconnected SQL transformations.
Output Fields
For stored procedures, stored functions, and saved queries, displays a preview of the SQL transformation output fields. For user-entered queries, configure output fields for the columns retrieved from the database.
For queries, the output fields also include the SQLError field, the optional NumRowsAffected field, and optional pass-through fields.
Advanced
Define advanced properties for the transformation. Advanced properties differ based on the type of SQL that the transformation processes.
Field name conflicts must be resolved in an upstream transformation. You cannot use field name conflict resolution rules in an SQL transformation.