When doing a data compare across result sets, I am seeing an issue where TOAD data point is not recognizing two identical comparison key values as equal, when they are.
In other words, the same exact comparison key value exists in both data sets, but the data compare is unable to match them together and reports them as orphan rows on both left & right side of the comparison results.
Example;
Result set 1 & 2 below are using the field "acct_num" as the comparison key. They both contain the key value "TRS~1xxxx8-000" :
Set 1 (left side of compare)
Set 2 (right side of compare)
Then using the acct_num field as the key:
The compare result will show these records as orhpans on the left and right side, even though we can see they are the same:
I tried converting both sides to string, and trimming spaces, but there was no change.
I then noticed that in between these orphan records, there is another orphan in the middle with a similar account ID. This record, TRS~1xxxx80, is an expected orphan - but I started to wonder if it might somehow be confusing the compare engine as it appears in between the other orphans, instead of after them.
So, I decided to try this again but without the TRS~1xxxx80 orphan, to see if this nuisance record might somehow be 'breaking' the compare engine. As you can see, on the right side results, that record has been removed:
And now, if I run the compare again using this result set, the comparison works for this account and shows it as equal, as expected:
I checked the other unexpected orphans and they seem to show the same situation. So it would seem when the key for an unrelated record is similar in this way to the other account keys that are truly equal, it causes them to fail to be matched up.
Thanks,
Dan
edit: acct #s were obfuscated.