Re: finding differences between 2 files

On 06-Feb-2015 11:43 -0600, Smith, Mike wrote:

Trying to identify
• Records in file a not in file b
• Records in file b not in file a
• Records that match key but have any differences

This is what I'm trying

( select * from prodfiles/ufxd
where ufact = ufact
except
select * from qtemp/ufxd
)
union
( select * from qtemp/ufxd
except
select * from prodfiles/ufxd
where ufact = ufact
)

The predicate in each WHERE clause [above], as coded, is always true. Perhaps the predicate was intended to be coded as an equivalence test between the column name and a [host] variable?; e.g.:
WHERE UFACT=:ufact

If this works as I think, I need to identify whether the results are
in file a or file b

Not sure what to do

To identify all that was noted, with a slight variation to the given query, the following would produce a report that can be reviewed for the differences [note: I removed the WHERE clause]:

Select
'In A, not in B' as Whence
/* optionally adding\un-commenting the following line: */
/* , AxB.Key1, ..., AxB.KeyN */
, AxB.*
FROM
( select A.* from prodfiles/ufxd as A
except
select B.* from qtemp/ufxd as B
) as AxB
union
Select
'In B, not in A' as Whence
/* optionally adding\un-commenting the following line: */
/* , BxA.Key1, ..., BxA.KeyN */
, BxA.*
FROM
( select B.* from qtemp/ufxd as B
except
select A.* from prodfiles/ufxd as A
) as BxA
order by key1, ..., keyN, Whence

Any two successive rows with the same values for the keys [1 to N] for which also each of those two rows was derived from a different file [i.e. different "WHENCE" values] will present the differences of the data in each column of a report; compare the values of each column of those two rows. When the "WHENCE" value is unchanged from the prior row of the report, then either a row with that key was deleted or a row with that key was added.