Wow, folks a lot of (good) ideas. I am already trying to consider the best approach, given all the input and the nature of records (layout, data, count/volume).
After I sent the message this AM, I went out for a run and began to think about the approach Karen described. I suppose it wasn't an "HP", but rather an RB moment", eh'? I had also given some clock ticks yesterday to something along the lines of Alastair's suggestion. However, this "SUBTRACT" approach had never occurred to me and surely would have remained so much undimensioned mental aether. So, thanks for the enlightening possibility. Again, thanks to everyone for the help, Steve in Memphis -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Doug Hamilton Sent: Tuesday, June 08, 2010 9:24am 09:24 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Record-To-Record Equality Test? Steve, what about the SUBTRACT command? Since it only works on tables, including temp tables, you would need to project your view to a temp table and subtract it from the original table. SET ERROR MESSAGE 2038 OFF DROP TABLE Exception1 DROP TABLE Exception2 DROP TABLE Exceptions SET ERROR MESSAGE 2038 ON PROJECT TEMPORARY ProdViewTbl FROM ProductionView USING * SUBTRACT ProdViewTbl FROM LocalTable FORMING Exception1 SUBTRACT LocalTable FROM ProdViewTbl FORMING Exception2 UNION Exception1 WITH Exception2 FORMING Exceptions That should give you a table, Exceptions, with the PK and the corresponding row from each table where there is a difference; i.e. two rows for each PK - one row from ProdViewTbl and one row LocalTable. Sort by the PK and compare the rows. Drawbacks: The "Exception" tables are permanent tables and could bloat the db after numerous runs. If you do a daily reload, NBD. I tested the concept on two 2-column tables with 409 rows each; I don't know how well (quickly) it might work on larger tables. Judicious indices would help. Your mileage may vary. Doug Wills, Steve wrote: > Does anyone know of a means to compare two records, in toto, for equality||inequality? > > The records have exactly the same structure, including column names and PK, and have a 1:1 relationship. > > One record comes from a local table and represents the data as it was at its original INSERTion or most recent UPDATE. The other record is in a view, populated by the data as it currently exists in a production database. It might or might not have changed since the most recent refresh of the local data. > > I would like to find a way to take a "management by exception" approach to by means of a record-by-record comparison between the pair of records. Then, if there is a difference, dig into the nature of the inequality. > > IOW, for any PK, is #RecordInLocalTable = #RecordInProductionView? If equal, get next PK-matched pair of records. If not, then lather, rinse, repeat. > > Thanks, > Steve in Memphis > >
