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

Hi everyone
It looks like I have to wrap each field with coalesce
Lets say that FIELD4 is the field that is null

Changing the query to
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
Coalesce(A.FIELD4 =, ' ') = coalesce(B.FIELD4, ' '))
with data
ON REPLACE DELETE ROWS;

This works, but to be on the safe side, I will need to wrap EACH field with coalesce


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: Sunday, January 17, 2021 4:09 PM
To: 'midrange-l@xxxxxxxxxxxxxxxxxx' <midrange-l@xxxxxxxxxxxxxxxxxx>
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