Validation Activity (2.9)
Introduction
A validation activity examines a staging table and validates the fields in it. For each field, the options depend on the data type and the function used for validation. For each field, you can also specify how to handle a validation failure.
The results of the validation checks are written to a validation report file.
Options
For each field to be validated, the following options apply:
-
Function Type—Specifies how to validate the field; see Validation Functions.
-
Validation Name—Specifies a short name for this field transformation.
-
Result Field Name—Specifies a name for the report field that will store either 1 (if the field value passes the validation rule) or 0 (if it does not). It is useful for the name to indicate the field being examined, for example CheckItemCount.
-
Validation Failure—Specifies how the system should respond when it encounters a field value that fails this specific rule. A Fatal failure halts the recipe. A Warning failure logs a warning. Both kinds of failures are visible in the Workflow Inbox.
There are additional options depending on Function Type; see the next section.
Validation Functions
The available validation functions are as follows:
Available for string and numeric fields. For this function, the field value fails validation if it does not match a value in a code table. Also specify:
-
Code Table Name—Specify the name of the code table to use.
-
Code Table Key Column—Specify the name of the key column in this table.
Available for all types of fields. For this function, the field value fails validation if an SQL predicate expression returns false. (You can, for example, compare the value in this field to the value in another field in the same record.) Also specify:
-
SQL Operator—Select a comparison operator.
-
SQL Expression—Enter a scalar SQL expression such as a field name or a constant.
Available for string fields. For this function, the field value fails validation if it does not match the given pattern. Also specify:
-
Glob Pattern—Specify a glob pattern.
-
Escape Character—Specify an escape character.
This function uses the InterSystems SQL %MATCHES predicateOpens in a new tab.
Available for string fields. For this function, the field value fails validation if it does not match the given pattern. Also specify Pattern This function uses the InterSystems SQL %PATTERN predicateOpens in a new tab.
Available for string fields. For this function, the field value fails validation if it does not match the given regular expression. Also specify Regular Expression This function uses the ObjectScript $MATCH functionOpens in a new tab.
Available for string fields. For this function, the field value fails validation if it does match the given regular expression. Also specify Regular Expression This function uses the ObjectScript $MATCH functionOpens in a new tab.
Available for all types of fields. For this function, the field value fails validation if it is null.
Available for numeric, date, and time fields. For this function, the field value fails validation if the value obtained in this recipe run is too different from the value in the previous run of the recipe. Also specify Tolerance—the amount by which the value is permitted to be different from the previous value. This can be either an absolute value or a percentage. (To specify a percentage, include a percent sign at the end.)
If the field is a date field, the unit is a day. If the field is a datetime field, the unit is a second.
Available for numeric fields. For this function, the field value fails validation if it falls outside of a fixed range of numeric values. For the Fixed Numeric Range function type, also specify:
-
Minimum— Minimum permitted numeric value.
-
Maximum— Maximum permitted numeric value.
-
Tolerance—Specify the tolerance, or the amount by which the value is permitted to be outside of the specified range. This can be an absolute value or a percentage. (To specify a percentage, include a percent sign at the end.)
Available for string fields. This function is intended for the scenario where there is a code table that represents an ordered sequence such as a set of ratings, and the goal is to check that the field value has not changed too much within that sequence, since the previous successful load of data. For example, in this code table, the rating AAA may be represented as 10, the rating AA+ may be represented as 9, and so on. To use this function type, also specify:
-
Tolerance—Specify the tolerance, or the amount by which the value is permitted to be outside of the specified range. This can be an absolute value or a percentage. (To specify a percentage, include a percent sign at the end.)
-
Code Table Name—Specify the name of the code table to use.
-
Code Table Key Column—Specify the name of the key column in this table. The key values are treated as strings.
-
Code Table Value Column—Specify the name of the value column in this table. The values in this column must be numeric.
This function examines the previous and current values of the field, both of which are strings such as AAA and AA+, converts them to the corresponding numbers, and compares the numbers, taking the tolerance into account. If the numbers match or are within the tolerance amount of each other, the new field value passes validation.
Available for all types of fields. For this function, the field value fails validation if the value is different from the value obtained in the previous successful run of the recipe. Use this rule if you expect the value to always be the same.
