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

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.