SQL & PL/SQL

Hi all,

I can't seem figure out why the following statement cause both SqlPlus and Toad (and originally our binaries) hang up.

WHERE t1.delflg = 0
AND t1.reltypcd = t2.typcd
AND t2.prctypcd = 1003
AND t1.lstmdts BETWEEN '21-Oct-2006' AND '13-Nov-2006'
AND 1 = (SELECT COUNT(t4.evntid) FROM reportdefinition t3, event t4 WHERE t1.evntid = t4.evntid AND t3.evnttypcd = t4.typcd AND t3.typcd = 1463)
AND 2 <= (SELECT COUNT(t5.evntid) FROM eventrel t5, eventdefinition t6 WHERE t1.evntid = t5.evntid AND t5.reltypcd = t6.typcd AND t6.prctypcd = 1003 AND t5.stscd = 3)
AND 1 <= (SELECT COUNT(t7.evntid) FROM eventrel t7, eventdefinition t8 WHERE t1.evntid = t7.evntid AND t7.reltypcd = t8.typcd AND t8.prctypcd = 1003 AND t7.rolcd = 0 AND t7.stscd = 3)
AND 2 <= (SELECT COUNT(t5.evntid) FROM eventrel t5, eventdefinition t6 WHERE t1.evntid = t5.evntid AND t5.reltypcd = t6.typcd AND t6.prctypcd = 1003 AND t5.stscd = 3)
AND 1 <= (SELECT COUNT(t7.evntid) FROM eventrel t7, eventdefinition t8 WHERE t1.evntid = t7.evntid AND t7.reltypcd = t8.typcd AND t8.prctypcd = 1003 AND t7.rolcd = 0 AND t7.stscd = 3)
AND 1 <= (SELECT COUNT(t9.evntid) FROM eventrel t9, eventdefinition t10 WHERE t1.evntid = t9.evntid AND t9.reltypcd = t10.typcd AND t10.prctypcd = 1003 AND t9.rolcd = 1 AND t9.stscd = 3);

The statement runs fine with I don't include both of the last criteria:
AND 1 <= (SELECT COUNT(t7.evntid) FROM eventrel t7, eventdefinition t8 WHERE t1.evntid = t7.evntid AND t7.reltypcd = t8.typcd AND t8.prctypcd = 1003 AND t7.rolcd = 0 AND t7.stscd = 3)
AND 1 <= (SELECT COUNT(t9.evntid) FROM eventrel t9, eventdefinition t10 WHERE t1.evntid = t9.evntid AND t9.reltypcd = t10.typcd AND t10.prctypcd = 1003 AND t9.rolcd = 1 AND t9.stscd = 3);

The only difference between these two criteria is in the value input into the rolcd.
It does not matter how big or small the statement is (ie. if I chop off other criteria in the Where clause), the statement will still cause a hang up as long as both of the above
criteria exist. I don't see any problems with the criteria per se. The hang up only happens on the client; the database server is not affected.

The full statement runs fine in MS Sequel server. All tables are indexed properly.
Possible issues could be:
1. The two criteria statement cause a dead lock. But how? If so, why the server is not affected?
2. Data corruption? Which I don't think this is the case since I was able to pull data out
of the database.
3. Too many aliases to confuse Oracle?


I appreciate all your help and inputs.
Please let me know if you need more info on this issue.

Thanks!