× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.