Data Compare for numbers and dates

Toad Data Point
1

Hi,

I use data compares a lot since it seems I have been involved in many upgrade projects lately. Two things I struggle with the most are number rounding and date formatting. In some cases this causes me to write sqls ahead of time to export data to new tables in order to compare. Ideally I would be able to write formulas directly in the compare. A couple of examples, one system does even number rounding so that a number such as 12.135 is presented as 12.13 and another system is presenting this same calculation as 12.14. I want to consider these a match if they are within 0.01 of each other. Another issue that happens to me a lot is when one system is presenting Timestamps and the other Dates. I would love to add a formula on one side or the other to convert the data element. I am currently using Toad Datapoint 3.8.0.575

Thanks in advance for any tips you might be able to give me.

Bob

2

For date comparisons, truncate the time: Oracle, Trunc(YourDateTimeField); SQL Server, Cast(YourDateTimeField as Date). For the numbers the best I can think of is where ABS(Number1 - Number2) <= 0.01

3

Thanks so much for your reply Greg. But, how do I enter these formulas into the compare utility?

I see that I can add filters for the data as a where clause but I cannot see anyplace to add functions for the data items.

4

Sorry, I was thinking of comparing data in Sql. I have not used the Compare utility in Toad.

5

You can create views utilizing those functions, and pick views to compare.

6

Hi bobby,

I created a task for improvement of our compare dialog to set formatting formulas for each column before compare. Task number is QAT-7313.

Also I suggest you to try comparing directly from our editor. When you right-click on the result grid you are able to compare these results. So you can prepare your own SQL results for comparison.

7

Great tip. Doesn’t work for me because our dba doesn’t allow me to create views so I end up waiting for them.

8

Thanks Filip. This makes sense. With TDP I can create result sets and use them like views since this functionality is allowed by TDP.