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

Calling an unconnected SQL transformation from an expression

Calling an unconnected SQL transformation from an expression

Call an unconnected SQL transformation from an Expression transformation with an :SP expression.
When you call a stored procedure from an expression, you configure the expression to return the stored procedure output values to fields in the expression. Use one of the following methods to return the output values:
  • Assign the output value to a local variable field.
  • Assign the output value to the system variable PROC_RESULT.
When you use the PROC_RESULT variable, Data Integration assigns the value of the return parameter directly to the output field, which you can write to a target. You can also assign one output parameter to PROC_RESULT and the other parameter to a variable.
Use expression variables to access OUT or INOUT parameters in the stored procedure. If the stored procedure returns multiple output parameters, you must create variables for each output parameter.
Use the following syntax to call a stored procedure in an expression:
:SP.<SQL transformation name> (arg1, arg2, PROC_RESULT)
If the stored procedure returns a single output parameter or return value, use the reserved variable PROC_RESULT as the output variable.
For example, the following expression calls a stored procedure called GET_NAME_FROM_ID:
:SP.GET_NAME_FROM_ID(inID, PROC_RESULT)
inID can be either an input field in the stored procedure or a variable in the Expression transformation. When you run the mapping,
Data Integration
applies the value of PROC_RESULT to the output field for the expression.
If the stored procedure returns multiple output parameters, you must create expression variables for each output parameter. For example, if the stored procedure also returns a title, create a variable field called varTitle1 in the Expression transformation and use the field as the expression for an output field called Title. You write the following expression:
:SP.GET_NAME_FROM_ID(inID, varTitle1, PROC_RESULT)
The following image shows how you configure the Expression transformation:
Data Integration
returns output parameters in the order they are declared in the stored procedure. In this example,
Data Integration
applies the value of the first output field in the stored procedure to varTitle1 and passes it to the Title field in the Expression transformation. It applies the value of the second stored procedure output field to the output field for the expression.
The data types for the expression fields and variables must match the data types for the stored procedure input/output variables and return value.