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. Appendix A: Datatype Reference
  22. Appendix B: Reporting Views
  23. Appendix C: Metadata Import Syntax
  24. Appendix D: Jasper Reports
  25. Appendix E: Glossary

Data Validation Option User Guide

Data Validation Option User Guide

Single-Table Constraint Tests

Single-Table Constraint Tests

The following table describes the single-table constraint tests:
Test
Description
COUNT
Compares the number of non-null values for the selected field to the constraint value. This test works with any datatype.
COUNT_DISTINCT
Compares the distinct number of non-null values for the selected field to the constraint value. This test works with any datatype except binary.
COUNT_ROWS
Compares the total number of values for the selected field to the constraint value. This test counts nulls, unlike the COUNT and COUNT_DISTINCT tests. This test works with any datatype.
MIN
Compares the minimum value for the selected field to the constraint value. This test works with any datatype except binary.
MAX
Compares the maximum value for the selected field to the constraint value. This test works with any datatype except binary.
AVG
Compares the average value for the selected field to the constraint value. This test can only be used with numeric datatypes.
SUM
Compares the sum of the values for the selected field to the constraint value. This test can only be used with numeric datatypes.
VALUE
Examines the values for the field, row by row, and compares them to the constraint value. This test works with any datatype except binary.
FORMAT
Determines whether the values in the field match the pattern that you use as the constraint value. The PowerCenter Integration Service uses the REG_MATCH function for this test. You cannot use this test with binary datatypes.
If you choose to use a pattern, use the perl compatible regular expression syntax. Enclose the pattern within single quotation marks.
For example, to test the format of a telephone number of the form xxx-xxx-xxxx, use the following pattern:
\d\d\d-\d\d\d-\d\d\d\d
The test passes if the phone number is 049-157-0156 but fails if the phone number is 0491-570-156.
To test a value that consists of a number followed by two alphabets and another number, use the following pattern:
\d[a-z][a-z]\d
The test passes if the alphanumeric string is 1ab2 but fails if the alphanumeric string is a1b2.
UNIQUE
Confirms that the value in the field is unique. This test does not use a constraint value. This test cannot be used with binary datatypes.
NOT_NULL
Confirms that the value in the field is not null. This test does not use a constraint value. This test cannot be used with binary datatypes.
NOT_BLANK
If the value in the field is a string value, this test confirms that the value in the field is not null or an empty string. If the value in the field is a numeric value, this test confirms that the value in the field is not null or zero. This test does not use a constraint value. This test cannot be used with datetime or binary datatypes.