When doing regression testing – I need to prove that the changes that I have done to two queries returned the same result sets.
If result sets are on the same database then you can do a MINUS operator.
If not – it can be a pain to shift result sets from database to database; especially if they are large.
For quick and dirty testing; I like using the tsql function CHECKSUM and pl sql function ORA_HASH. It can give you some quick results to test whether a column of data has a high probability of been the same as another column.
To test a result set; I have the following excel formulas saved in my “Handy formulas” notebook:
TSQL
=",SUM(CAST(CHECKSUM("&A1&") AS BIGINT)) AS "&A1
PL/SQL
=",SUM(ORA_HASH("&A1&")) AS "&A1
With a list of column name you can then make an checksum sql statement from Excel
Running this query on both result sets; differences can be found by seeing if there is a difference in the retuned values
