Re: Reformat SQL Syntax to for a correlated SQL query?

Originally posted by Robert Schultz
> 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,
> (SELECT MIN(tableThree.createDate)
> 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
> FROM
> (SELECT tableSeven.anotherID,
> (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,
> (SELECT tableTen.createDate
> FROM tableTen, tableEleven
> WHERE tableTen.tableElevenID =
> tableEleven.tableElevenID
> AND tableEleven.nameType = 'Special Chars Again'
> AND tableTen.aBigID = tableSeven.aBigID) secondDate,
> FROM tableSeven
> 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?

I really struggle to follow your example - in fact, I can't.

However, I suspect your problem comes from the fact that you can't correlate an in-line view. For example, you can't do this:

select e.ename, v.dname
from emp e,

       (select * from dept where dept.deptno = e.deptno) v where e.deptno = v.deptno
/

(Not that I can imagine why you would want to either!)

--
Posted via http://dbforums.com