I do not see how either of UNION ALL or UNION DISTINCT could replace the original query, unless more about the files and\or data are either known or are to be assumed. If there are duplicate rows using just the parenthetical predicate before the 'OR', then the DISTINCT processing would cause those duplicates to disappear in the UNION query. And as already alluded in the quoted message... If there are duplicate rows added by the UNIONed data using the parenthetical predicate after the 'OR', then duplicates that would not have appeared in the original query would be included in the UNION ALL query.

Regards, Chuck

On 08-Jun-2011 06:41 , Morgan, Paul wrote:
Finishing up on what Dan suggested replacing the OR with a UNION. No
ALL on the union which would create duplicate rows and adding in the
third exists:

SELECT E.PRMNBR, E.EVTDTE, E.EVTTME, E.USRID, E.EVTRMK
FROM P1FILES.KSEVT E
WHERE EVTRMK LIKE 'CIS CONTRIB. ACCT #%'
AND EXISTS( SELECT *
FROM P1FILES.KSEVT
WHERE PRMNBR = E.PRMNBR
AND EVTID<> E.EVTID
AND EVTRMK LIKE 'CIS CONTRIB. ACCT #%' )

UNION

SELECT E.PRMNBR, E.EVTDTE, E.EVTTME, E.USRID, E.EVTRMK
FROM P1FILES.KSEVT E
WHERE EXISTS( SELECT *
FROM P1FILES.KSEVT
WHERE PRMNBR = E.PRMNBR
AND EVTID> E.EVTID
AND EVTRMK LIKE 'CIS CONTRIB. ACCT #%' )
AND EXISTS( SELECT *
FROM P1FILES.KSEVT
WHERE PRMNBR = E.PRMNBR
AND EVTID< E.EVTID
AND EVTRMK LIKE 'CIS CONTRIB. ACCT #%' )

I'd be curious if this also hangs...


This thread ...

Replies:

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

This mailing list archive is Copyright 1997-2019 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].