Re: SQL Comparing two identical files for differences with log file

I've run a couple scenarios through Visual Explain, and come up with an
interesting find. I ran the files as a join on the two key fields that
make a unique record, and comparing the file's fields, thinking that
should be the best method. I then ran Visual Explain for a portion of what
I did below, basically as a "SELECT * FROM A EXCEPT SELECT * FROM B". The
Visual Explain had the same process, but chose a different logical. The
logical it chose was one with a whole bunch of keys on it (about 12).
What's interesting is that I was assuming that I should join the two files
on the smallest key set necessary to get a unique record from both files,
but now that I think about it, if I'm only looking for one record that
doesn't match in another file, what I'd really want is something like an
index with every file column, or at least quite a few columns. So, I'm
thinking that EXCEPT made a better choice than what I was making in my
limited thinking. I plan to apply this to a real production dataset, and
see what Visual Explain recommends for indexes.

In conclusion, EXCEPT is a lot smarter than I was giving it credit for.


___________________________________
Darren Strong
Dekko



"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 05/18/2017
08:47:42 AM:

From: Darren Strong <darren@xxxxxxxxx>
To: "midrange-l " <midrange-l@xxxxxxxxxxxx>
Date: 05/18/2017 08:48 AM
Subject: SQL Comparing two identical files for differences with log file
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

We have a requirement to create an audit log of key values within a file

that register differences. This will be used downstream to update other
files in a data warehouse type of system. Secondly, the actual file
difference need to be synced. So, I've done the following with the

EXCEPT

keyword as a driver. I believe this performs well when looking for

missing

values based on a key, but please noticed where I do something like
"select * from a except select * from b". This is to get records that
exist in both files, but have some kind of record difference that needs

to

be updated. I assume that this is a really brute force method of

figuring

out what records don't match, so I wondered if there was a better way.
Ideally I would think something like a "join" of the two files, and then

saying "where a.* <> b.*", but that kind of syntax isn't available.

create table darren/sild as(
with t1 as(
select i1.ILINVN,i1.ILLINE,'D' as OPER
from (select ILINVN,ILLINE from ERPLXFINF/SIL
except select ILINVN,ILLINE from ERPLXF/SIL) i1
union
select i2.ILINVN,i2.ILLINE,'I' as OPER
from (select ILINVN,ILLINE from ERPLXF/SIL
except select ILINVN,ILLINE from ERPLXFINF/SIL) i2
)
select * from t1
union all
select T2.ILINVN,T2.ILLINE,'U' as OPER from
(select * from ERPLXFINF/SIL except select * from ERPLXF/SIL) T2
where (T2.ILINVN,T2.ILLINE) not in (select ILINVN,ILLINE from T1)
) with data


___________________________________
Darren Strong
Dekko

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate

link:

http://amzn.to/2dEadiD