Table of Contents

Search

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

Data Validation Option User Guide

Data Validation Option User Guide

Table-Pair Tests

Table-Pair Tests

The following table describes the table-pair tests:
Test
Description
COUNT
Compares the number of non-null values for each of the selected fields. This test works with any datatype. The fields you compare must be of the same general datatype, for example, numeric- to-numeric or datetime-to-datetime.
COUNT_DISTINCT
Compares the distinct number of non-null values for each of the selected fields. This test works with any datatype except binary. The fields you compare must be of the same general datatype, for example, numeric- to-numeric or datetime-to-datetime.
COUNT_ROWS
Compares the total number of values for each of the selected fields. This test counts nulls, unlike the COUNT and COUNT_DISTINCT tests. This test works with any datatype.
MIN
Compares the minimum value for each of the selected fields. This test works with any datatype except binary. The fields you compare must be of the same general datatype, for example, numeric- to-numeric or datetime-to-datetime.
MAX
Compares the maximum value for each of the selected fields. This test works with any datatype except binary. The fields you compare must be of the same general datatype, for example, numeric- to-numeric or datetime-to-datetime.
AVG
Compares the average value for each of the selected fields. This test can only be used with numeric datatypes.
SUM
Compares the sum of the values for each of the selected fields. This test can only be used with numeric datatypes.
SET_AinB
Determines whether the entire set of values for Field A exist in the set of values for Field B. This test works with any datatype except binary/other. The fields you compare must be of the same general datatype, for example, numeric- to-numeric or datetime-to-datetime. You can use this test to confirm that all values in a field exist in a lookup table. This test examines all values for a column instead of making a row-by-row comparison.
SET_BinA
Determines whether the entire set of values for Field B exist in the set of values for Field A. Determines whether the entire set of values for the field selected from Table B exist in the set of values for the field selected from Table A. This test works with any datatype except binary/other. The fields you compare must be of the same general datatype, for example, numeric- to-numeric or datetime-to-datetime. You can use this test to confirm that all values in a field exist in a lookup table. This test examines all values for a column instead of making a row-by-row comparison.
SET_AeqB
Determines whether the set of values for the selected fields are exactly the same when compared. This test works with any datatype except binary. The fields you compare must be of the same general datatype, for example, numeric- to-numeric or datetime-to-datetime. You can use this test to confirm that all values in a field exist in a lookup table. This test examines all values for a column instead of making a row-by-row comparison.
SET_ANotInB
Determines whether there are any common values between the selected fields. If there are common values, the test returns an error. If there are no common values, the test succeeds.
VALUE
For joined table pairs, this test compares the values for the fields in each table, row-by-row, and determines whether they are the same. If there are any rows that exist in one table but not the other, the rows are disregarded which implies an inner join between the tables. If the fields are both null and the
Null=Null
option is disabled, this pair of records fails the test. This test works with any datatype except binary. The fields you compare must be of the same general datatype, for example, numeric- to-numeric or datetime-to-datetime.
OUTER_VALUE
For joined table pairs, this test compares the values for the fields in each table, row-by-row, and determines whether they are the same. If there are any rows that exist in one table but not the other, they are listed as not meeting the test rules which implies an outer join between the tables. For the test to pass, the number of rows for the tables, as well as the values for each set of fields must be equal. If the fields are both null and the
Null=Null
option is disabled, this set of records fails the test. This test works with any datatype except binary. The fields you compare must be of the same general datatype, for example, numeric- to-numeric or datetime-to-datetime.