Re: Comparing an extract file created today, to an extract file created yesterday

On Jan 17, 2021, at 3:08 PM, Alan Shore via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

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

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