Select columns to display in the validation checks listings page

Identify the columns you want to display in the Validation Check Listings page. Include any columns the validation check will operate on. The system creates a SELECT clause for the query based on these selections.

You also select the discrepant table and column that contains the descrepant data that you want checked. In addition to a discrepant column, you can add one or more secondary columns. With secondary columns, Oracle DMW can verify its data against the validation check and check for changes. The action taken on the discrepancy depends on the state of the discrepancy. For more details, see Secondary columns.

Note:

When you view the VC listings, you see the discrepant column header appear with an asterisk (*) and secondary discrepant column headers appear with two asterisks (**) so you can easily identify them.

1. Continuing in the Add Validation Check window, in the Source pane, expand the node for the table or tables with data you want to display in the Validation Checks Listings page.

   Note:

   Tables and columns marked Not Used in the transformation are not displayed here, nor are uninstalled tables.

2. Select columns by moving them into the Selected Columns tab. You can work several ways:

   • Drag and drop selected columns or tables.

   • Select columns or tables and click the Add to Select statement icon.

   • To write an expression that operates on multiple columns, add all columns in the expression to the same row:

     1. Move one column into Selected Columns and highlight it there.

     2. Select the additional columns and click the Use in expression arrow icon.

3. Select a Table Alias for each column used in an expression. If there is only one defined for the table, the system adds it automatically.

   To add an alias, go to the Table Alias tab. See Add table aliases.

4. Enter the Column Header for Display. This header is displayed in the Validation Checks Listings page.

5. Expression: Add an expression, if needed, to change data display, for example, to mask blinded data, or to append a string to all values in the column, or to concatenate two column values in a single column. Either:

   For example, to prefix ‘Test' before the SUBJID, write: Select ‘Test'+SUBJID.

   • Enter free text.

     Important:

     • If you reference a static package or function in free text, you must select it in the Selected Packages tab.

     • In free text, use just the column name, not the table.column format, unless you need to use an alias, as in a self-join. In that case the alias.column format is required.

   • Click the Modify Expression icon; see Use the Expression Builder for details. You can edit code generated by the Expression Builder in this field afterward.

     If you use the Expression Builder, you do not need to use the Select Packages tab.

     Note:

     If you select a column that contains masked data, write an expression for the column to display masking values. Validation checks can evaluate real data that is masked and create a discrepancy on it. The target table is blinded if the source table supports table/column/cell/row level blinding. Since target tables are auto generated and they are not accessible in DMW other than to see the data in the listing. So, we cannot define row/column/cell level blinding on custom listing and VC's target table. The target table is authorized when the authorize attribute is set for custom listing and VC.

6. Discrepant Table and Column: In the upper portion of the window, designate one data item as the one against which discrepancies are created. If the validation check logic processes two or more data items, select one of them.

   Tip:

   You must specify Selected Columns before you can specify the primary table or column.
   1. In the Discrepant Table field, select the table that contains the discrepant data.
   2. In the Discrepant Column field, select the column that contains the discrepant data. (When you view a VC listing, the discrepant column appears with an asterisk next to the column name so you can easily identify it.)
7. If you want to add one or more secondary columns to verify its data against the validation check, add the columns you want to use. Then, select the Secondary Column check box next to one or more columns that you want to use as secondary columns. Do not select the column already used as the discrepant column as a secondary column. Then click OK.

Next: Specify validation check criteria.