I'm doing a query similar to this in structure, and it currently works fine in Oracle 8.1.7:
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 my tableOne 'outer query' reference is now two levels deep and Oracle complains.
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?