Data Compare results showing same record is only in source and only in target when there is no mismatch (4249047)

Data Compare results showing same record is only in source and only in target when there is no mismatch

When comparing two tables from two different database platforms (Oracle and SQL Server, for example), it is indicating that a number of records are only on the source and the same number of records are also only on the target.  So, the same record shows that it is only on the source and only on the target, when there is no mismatch.

When looking at the records, the keys look completely identical (and there is no duplicates either on the source or the target).

The problem is with the data position which is used for comparing. The Key column is used for sorting data and then the data is compared in the same position. Unfortunately, different providers sort data a little bit different.

Source for Oracle:


Target for SQL Server:


Looking at these examples, it can be seen that the position is different. Because data is read one by one row it did not see same results on first row, so this row is marked as unique. In the next step the next row is read, but only for the target because we are trying compare next row. These rows are equal, but there is still one more row on source. Because there is no other data on target this row is marked as unique on Source.

RESOLUTION #1:
Compare directly from the Editor and compare data from here. Right-click on the results and CompareTo with different result set from another Editor. Because we are using our types in our FastTable, sorting is same. Only disadvantages are that we have to read all data before compare, the compared results are not so detailed, and you are not able get sync script. 

RESOLUTION #2:
Select a different key for the comparison.  Since it will sort on a different column with a different set of data, it may match up better during the compare.