Oracle 8.1.7 SQL Correlated Subqueries
I'm doing a query similar to this in structure, and it currently works fine in Oracle 8.1.7:
But my tableOne 'outer query' reference is now two levels deep and Oracle complains.
SELECT tableOne.name, tableTwo.nameAgain,
FROM tableThree, tableFour
WHERE tableThree.tableFourID = tableFour.tableFourID
AND tableFour.nameType = 'Historical_Event'
AND tableThree.tableTwoID = tableOne.tableTwoID) oldestEvent,
(SELECT SUM(COUNT(DISTINCT tableFive.specialID))
FROM tableFive, tableSix
WHERE tableFive.tableSixID = tableSix.tableSixID
AND tableSix.nameType = 'Open_Encounter'
AND tableFive.tableFiveID = tableOne.tableFiveID
GROUP BY tableFive.specialID) numOfEncounters
FROM tableOne, tableTwo
WHERE tableOne.statusID = tableTwo.statusID
AND tableTwo.nameType = 'Status_Open'
AND tableOne.name != 'system_account'
ORDER BY tableOne.loginState DESC, tableOne.name
The problem is, I tried adding another subquery to the select area that itself has a subquery, example (just the subquery that fits into the query above):
(SELECT TO_CHAR(ABS(TRUNC(24*(firstDate - secondDate)))*60) +
LTRIM(TO_CHAR(ABS(TRUNC(24*60*(firstDate - secondDate)) -
(60*TRUNC(24*(firstDate - secondDate)))), '00')) minutesElapsed
(SELECT NVL(MAX(tableEight.createDate), sysdate)
FROM tableEight, tableNine
WHERE tableEight.tableNineID = tableNine.tableNineID
AND tableNine.nameType = 'Special Chars'
AND tableEight.aBigID = tableSeven.aBigID) firstDate,
FROM tableTen, tableEleven
WHERE tableTen.tableElevenID = tableEleven.tableElevenID
AND tableEleven.nameType = 'Special Chars Again'
AND tableTen.aBigID = tableSeven.aBigID) secondDate,
WHERE tableSeven.someIDAgain = tableOne.someIDAgain
AND tableSeven.createdDate > TO_DATE('07/01/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'))
But, I've been told that this is horrible Oracle SQL syntax and should be recoded anyways.
Problem is, in the Oracle documentation they talk about subqueries and correlated subqueries (Which I think I have since an inner query references an outer column) but none of them put the subqueries into the select portion (where it seems to just make sense).
I just don't see how this can be combined into a single statement because the seperate subqueries are doing a lot of aggregate functions on their own.
I could really use some help in this matter. How should I be writing this thing?