RE: Problem using OR in SQL Statement -- MIDRANGE-L

The system probably isn't hanging. As my own personal rule, I try to avoid the OR condition because of this reason. Your statement is probably trying to find the correct access path but with all LIKEs EXISTS, the NOT operator, and the OR, it probably ends up doing a complete table scan. You will need to rework your SQL to find the best performance.

Suggestion: Is there any way you can make your correlated sub queries into an non correlated?

Maybe try using a join, group by and having clause instead...
SELECT E.PRMNBR, E.EVTDTE, E.EVTTME, E.USRID, E.EVTRMK{, E.EVTID, MIN(F.EVTID), MAX(F.EVTID)} -- inside brackets is optional.
FROM P1FILES.KSEVT E
JOIN P1FILES.KSEVT F
ON E.PRMNBR = F.PRMNBR
AND SUBSTR(E.EVTRMK,1,19) = SUBSTR(F.EVTRMK,1,19)
WHERE SUBSTR(E.EVTRMK,1,19) = 'CIS CONTRIB. ACCT #'
GROUP BY E.PRMNBR, E.EVTDTE, E.EVTTME, E.USRID, E.EVTRMK, E.EVTID -- You will need to group on all these fields.
-- To handle the first part of the SQL. Where EVTID <> E.EVTID
HAVING E.EVTID not in (MIN(F.EVTID), MAX(F.EVTID))
-- To check to see if there are records existing where at least one record exists where E.EVTID is less than EVTID
-- AND a record where at least one record exists where E.EVTID is > EVTID.
OR NOT (E.EVTID BETWEEN MIN(F.EVTID) AND MAX(F.EVTID))

You may want to change the having clause to get the results you need. Let us know. Yes I do realize that I used OR in this statement. :-) LOL.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Graap, Kenneth
Sent: Tuesday, June 07, 2011 4:02 PM
To: Midrange Systems Technical Discussion (midrange-l@xxxxxxxxxxxx)
Subject: Problem using OR in SQL Statement

This SQL select statement containing an 'OR' within the Where clause will not run to completion, hangs until cancelled...

SELECT PRMNBR, EVTDTE, EVTTME, USRID, 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 #%'))

OR (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 #%'))

However, breaking the statement above apart and running the two parts separately does work...

Using the statement containing only the first portion of the Where clause, left of the 'OR', returns 390 rows...

SELECT PRMNBR, EVTDTE, EVTTME, USRID, 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 #%'))

Using the statement containing only the second portion of the Where clause, right of the 'OR', returns 1738 rows...

SELECT PRMNBR, EVTDTE, EVTTME, USRID, 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 #%'))

Does anyone know why this won't work with the OR ???????

This is being run on a V5R4M5 system...

[Description: Description: nwnatural]
Kenneth E. Graap | Systems Administrator keg@xxxxxxxxxxxxx<mailto:keg@xxxxxxxxxxxxx> 503-226-4211 x5537 http://www.linkedin.com/in/kennethgraap

________________________________

Notice from Bob Evans Farms, Inc: This e-mail message, including any attachments, may contain confidential information that is intended only for the person or entity to which it is addressed. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message and any attachments.