Table of Contents

Search

  1. Preface
  2. Introduction to Data Validation Option
  3. Repositories
  4. XML Data Source
  5. Tests for XML Data Sources
  6. Connections
  7. Expressions
  8. Table Pairs
  9. Tests for Table Pairs
  10. Single-Table Constraints
  11. Tests for Single-Table Constraints
  12. Examples of Tests from Spreadsheets
  13. SQL Views
  14. Lookup Views
  15. Join Views
  16. Aggregate Views
  17. Business Intelligence and Reporting Tools Reports
  18. Dashboards
  19. DVOCmd Command Line Program
  20. Troubleshooting
  21. Datatype Reference
  22. Reporting Views
  23. Metadata Import Syntax
  24. Jasper Reports
  25. Glossary

Data Validation Option User Guide

Data Validation Option User Guide

Using PowerCenter User-Defined Functions in Data Validation Option

Using PowerCenter User-Defined Functions in Data Validation Option

You can create a user-defined function in PowerCenter and use it in Data Validation Option.
  1. In the Data Validation client, right-click the target PowerCenter repository and select
    Edit Repository
    to view the name of the folder where you can create the user-defined function.
    The following image shows the target folder for the PowerCenter repository: The following image shows the target folder for the PowerCenter repository.
  2. In PowerCenter, go to the target folder of the repository that you use for Data Validation Option.
    The following image shows the properties of the user-defined function:
    Enter the name and create the expression for the user-defined function.
  3. Save the user-defined function and disconnect from the target folder.
    Data Validation Option generates and writes objects to the target folder. If the target folder is open in the PowerCenter repository, a locking error occurs.
  4. In Data Validation Option, go to the location where you want to use the user-defined function.
    You can call the user-defined function from any valid location, such as a WHERE clause, an expression in a value test, or a test condition.
    To call the user-defined function, use the syntax
    :UDF.<function name>(<parameters>)
    For example, if the name of the function is ALLTRIM, use the expression
    :UDF.ALLTRIM(FIRSTNAME)
    The following image shows the user defined function called from an expression in a value test:
  5. Ensure the data type, precision, and scale are appropriate for the function and match the return type correctly.
  6. Save and run the test.