Horrible Oracle SQL syntax? I'd say it's nice but you misuse it horribly.
Look at this:
Code:
select dept.deptno,
(select count(*) from emp where emp.deptno=dept.deptno) TheCount
from dept;

    DEPTNO   THECOUNT
---------- ----------
        10          2
        20          5
        30          5
        40          2
        25          0
This is an example of using correlated subquery. Works fine, counts employees for each department. The drawback is that it need to execute 6 queries instead of just one. If I'd write such query, I'd get rid of subquery:
Code:
select dept.deptno, count(emp.deptno)
from emp, dept
where emp.deptno(+)=dept.deptno group by dept.deptno;


    DEPTNO COUNT(EMP.DEPTNO)
---------- -----------------
        10                 2
        20                 5
        25                 0
        30                 5
        40                 2
One query, same results, readable code. Rewrite the query, do not use correlated subquery unless you really have to.
I mean something like that, put outer joins where appropriate:
Code:
SELECT tableOne.name, tableTwo.nameAgain,
       MIN(tableThree.createDate) oldestEvent,
       SUM(COUNT(DISTINCT tableFive.specialID)) numOfEncounters
FROM tableOne, tableTwo ,tableThree, tableFour, tableFive, tableSix
WHERE tableOne.statusID = tableTwo.statusID
AND tableTwo.nameType = 'Status_Open'
AND tableOne.name != 'system_account'
AND tableThree.tableFourID = tableFour.tableFourID
AND tableFour.nameType = 'Historical_Event'
AND tableThree.tableTwoID = tableOne.tableTwoID
AND tableFive.tableSixID = tableSix.tableSixID
AND tableSix.nameType = 'Open_Encounter'
AND tableFive.tableFiveID = tableOne.tableFiveID
GROUP BY tableOne.name, tableTwo.nameAgain
ORDER BY tableOne.loginState DESC, tableOne.name
Correlated subquery might be real performnace killer - just this week I came accross a similar query which had two-level correlated subquery. Executing on tables about 10,000 records took 20+ minutes. After rewriting in manner I suggested above, less than one second.