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> On Behalf Of Alan
Shore via MIDRANGE-L
Sent: Sonntag, 17. Januar 2021 22:09
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Alan Shore <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 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
midrange.com
