Re: Best way to compare records

[a4g atl <a4ginatl2@xxxxxxxxx>]

Digging into this I found the EXCEPT should work. It is similar to UNION
ALL in operation. I have to try it in the next few days.
Darryl.


On Wed, Aug 21, 2024 at 4:25 PM VERNON HAMBERG Owner via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

> 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
> <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.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:
> MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:
> MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:
> support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
>
> .
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.