Compare Tables Actions
Compare Tables actions perform a comparison of two Table datasets by comparing corresponding rows and columns in each of the tables. The results are stored in a Table dataset.
| Action Property | Description |
|---|---|
| Counts | A Table dataset containing a comparison summary for the compared tables. |
| Result | A Table dataset in which to store the comparison results. |
| Source1 | A Table dataset containing the first table to be compared. |
| Source2 | A Table dataset containing the second table to be compared. |
| SystemName1 | A String parameter or dataset specifying a name for the first system. If this property is set, it overwrites the ‘System1’ value in the Result dataset. |
| SystemName2 | A String parameter or dataset specifying a name for the second system. If this property is set, it overwrites the ‘System2’ value in the Result dataset. |
The Result dataset includes columns from the tables being compared, plus two additional columns. The first additional column indicates the comparison status for each table row, and the second additional column is named System.
-
For SAP tables that are compared, the System column contains the name of the table’s associated RFC Destination. These values are shown for rows that are different or in one table only.
-
For other tables that are compared, the System column contains the name of the Source1 or Source2 Table dataset. These values are shown for rows that are different or in one table only.
Values in the System column may be overwritten by setting the Compare Table action’s SystemName1 or SystemName2 properties.
The Comparison Status column may contain the following icons. Internally, the comparison status of each row is represented in the Result table by values in a column named ____Status. This column may be used in an Execute SQL action to filter the table comparison results.
| Icon | ____Status Value | Description |
|---|---|---|
|
4 | The row values are the same in each of the tables. |
|
11 or 12 | The row values are different in each of the tables. These rows are shown in red with a yellow background. |
|
1 | The row exists in the ‘Source1’ table only. These rows are shown with a cyan background. |
|
|
2 | The row exists in the ‘Source2’ table only. These rows are shown with a pink background. |
The Counts dataset has the following columns:
| Column | Description |
|---|---|
| Name | The name of the ‘Source1’ table. |
| NumRows | The number of rows in the table. |
| In1 | The number of table rows that exist only in the first system compared. |
| In2 | The number of table rows that exist only in the second system compared. |
| Same | The number of rows that are the same in the compared table. |
| Different | The number of rows that are different in the compared table. |
| Status | The comparison status for the tables. |
| Description | The name of the ‘Source1’ table. |
If a table has different fields on each of the compared systems, only the fields that are common to both systems will be compared. Tables may only be compared if they have the same key fields; these are shaded green in the Table dataset display. SAP tables normally have key fields, however tables that have not been derived from SAP may be compared by carrying out the following steps:
- Use two Execute SQL actions to copy the tables to two new Table datasets. Set the Options property for each of the actions to ‘CopyRows Source1 Result’.
- Edit the Statements property for each Execute SQL action, and use the following statement to add the same key fields to each of the result tables:
SET RESULT TABLE KEYS (<Field1>[, <FieldN>])
- The result tables may then be compared using the Compare Tables action.
Example:
To examine the details for a Compare Tables action, double-click the action node, or select ‘View Details’ from the node’s context menu. The Details screen displays the number of records in the Source and Result tables.