RE: [EXTERNAL] RE: Comparing an extract file created today, to an extract file created yesterday -- MIDRANGE-L

Thanks Birgitta - works like a charm

Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

From: Alan Shore
Sent: Monday, January 18, 2021 2:54 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] RE: Comparing an extract file created today, to an extract file created yesterday

Thanks for your reply Birgitta
This looks FAR simpler than my method
Will look into this in the morning

Sent via the Samsung GALAXY S® 5, an AT&T 4G LTE smartphone

-------- Original message --------
From: Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx<mailto:Hauser@xxxxxxxxxxxxxxx>>
Date: 1/18/21 00:48 (GMT-05:00)
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
Subject: [EXTERNAL] RE: Comparing an extract file created today, to an extract file created yesterday
Is an EXCEPTION JOIN the right way?
Wouldn't it be better to merge both results with EXCEPT?

The following query returns all rows that are in DELTA1 and not or different
in DELTA2 (all columns and all rows are compared)

Select * from FULLLOAD1
Except
Select * from FULLLOAD2

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to." (Richard Branson)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Alan
Shore via MIDRANGE-L
Sent: Sonntag, 17. Januar 2021 22:09
To: midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Alan Shore <ashore@xxxxxxxx<mailto:ashore@xxxxxxxx>>
Subject: Comparing an extract file created today, to an extract file created
yesterday

Hi everyone
We are on V7r3
I have an SQL query, that extracts data from a large number of files, to
create, what I call "full load" file This full load file is compared to the
"full load" file created yesterday, and creates a delta file, of records
that changed between yesterday and today This comparison is executed in the
following manner Lets say the file in question contains 4 fields

create or replace table QTEMP.DELTA as
(SELECT A.* FROM LIBRARY.FULLLOADTD A
EXCEPTION JOIN LIBRARY.FULLLOADYD B on
A.FIELD1 = B.FIELD1 and
A.FIELD2 = B.FIELD2 and
A.FIELD3 = B.FIELD3 and
A.FIELD4 = B.FIELD4)
with data
ON REPLACE DELETE ROWS;

Where LIBRARY.FULLLOADTD is todays file and LIBRARY.FULLLOADYD is yesterdays
file Then later in my program I copy/replace todays file into yesterdays
file The first time that I am running this - yesterdays file will be empty

In error, I ran the delta create again, just after I copied/replaced todays
file (LIBRARY.FULLLOADTD) to yesterdays file (LIBRARY.FULLLOADYD) To say
that I was surprised to see there was records in the delta file, is an
understatement Both files at that point should be EXACTLY the same -
therefore - there should be nothing in the delta To cut a LONG story short,
I noticed that the records in the delta file had fields that were null.
This is okay - but I believe its what is causing the delta.
Those records also had these null fields in both todays file AND yesterdays
file Does this make sense to anyone?
I didn't think that comparing 2 fields (both null) would be a difference If
this does make sense - does anyone know how this situation can be
circumvented so that there is no difference

As always, all answers gratefully accepted

Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

--
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.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com

--
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.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com