Re: Best way to compare records

I love the VALIDATE_DATA_FILE function - it does require 2 different tables - one could be a copy, as you say. But when do you make the copy? Where do you keep it? You need some way to keep track of changes - journaling or temporal come to mind. Things like the UPDATE ON CHANGE stuff tells you only that something HAS changed, not what WAS changed.


I can't say what has changed in me if I am only looking at myself, with no history logged in a dark recess of my mind.


So you can't compare a thing to itself if that is all you have - you'll find no changes.


So what am I missing in the expressed need?

Cheers
Vern


On Wed, 21 Aug, 2024 at 2:35 PM, Andrew Lopez (SXS US) <Andrew.Lopez@xxxxxxxxxxxxxxxxxx> wrote:


To: midrange-l@xxxxxxxxxxxxxxxxxx

I'm not sure this is what you are looking for, but Birgitta pointed me to the VALIDATE_DATA_FILE function or the use of the SQL Except when I asked how to compare two copies of a file when there could be differences in dozens of fields. The SQL did a great job of showing me all cases where the two files did not match. Unfortunately, I wasn't (and still aren't) on a release that supported the function referenced.

Look for her post from June 20th, 2023 entitled RE: Comparing Two Copies of a Physical File:
Have you had a look at the VALIDATE_DATA_FILE Table Function within the
SYSTOOLS Schema?
https://www.ibm.com/docs/en/i/7.5?topic=us-validate-data-validate-data-file-
validate-data-library-table-functions

Otherwise you may run the following query ... if it does not return any row
both tables are identical:

(Select * from Table1
Except
Select * from Table2)
Union
(Select * From Table2
Except
Select * From Table1);

_____________________________________________________________________
Spirax-Sarco Engineering Plc. This e-mail has been scanned for viruses by Cisco Cloud Email Security.