|
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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.