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

Table Pair Test Example

Table Pair Test Example

You want to compare the fields in a flat file and validate the sales against the data in the target Oracle table.
The following table displays the columns and data types in a flat file source:
Column
Data Type
EmployeeID
String(10,0)
LastName
String(20,0)
FirstName
String(20,0)
Designation
String(30,0)
Location
String(15,0)
State
String(5,0)
Pin
String(10,0)
Salary
String(20,0)
The following table displays the columns and data types in the Oracle target:
Column
Data Type
EmployeeID
number(p,s)(2,0)
LastName
varchar2(9,0)
FirstName
varchar2(10,0)
Designation
varchar2(24,0)
Location
varchar2(8,0)
State
varchar2(2,0)
Pin
number(p,s)(7,0)
Salary
number(p,s)(11,0)
To test the data stored in the source and validate against data stored in the target, perform the following steps:
  1. Create the table pair test.
    Select the flat file as Table A and the Oracle table as Table B.
  2. Create a table join. Select EmployeeId in Join Field A and Join Field B.
    Because the fields in the source and target are not of the same or compatible type, you cannot do a direct join. To convert the EmployeeId field in the source table from string to decimal data type, add an expression.
    The following image shows the expression:
    The following image shows the table pair editor:
    The image shows Table A as the flat file source and Table B as the Oracle table. The table join shows the join between the EmployeId field in the source and target. The EmployeeId field in the source is converted to a decimal data type.
  3. Create an OUTER_VALUE test.
    To compare EmployeeId in the source and the target table, create an OUTER_VALUE test. The test compares the values of the fields, and determines whether they are the same. Because the EmployeeId in source and target tables are of a different data type, add an expression to EmployeeId to convert it to decimal, and compare it with EmployeeId in the target table.
    The following image shows the table pair test editor:
    The OUTER_VALUE test shows EmployeeId from Table A selected in Field A and EmployeeId from Table B selected in B. The Expression Definition area shows that the EmployeeID in Field A is converted in a decimal data type.
    The test checks the contents of the target table against the contents of the source table.