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?

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

andrewst <member14183@dbforums.com> wrote in message news:<3112862.1058285965@dbforums.com>...
> 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

No views are involved.

Maybe I should simplify it better:
I'm doing something like this:

select e.ename, (select count(*) from dept where dep.deptno =
e.deptno) theCout
from emp e

But I'm doing it more advanced, and several of them.
It works fine as shown.

But, if the subselect itself contains ANOTHER subselect, which
references a column from a table from the main first statement, then
it gives an error.

See what I mean now?

On 17 Jul 2003 10:41:10 -0700, sembiance@hotmail.com (Robert Schultz)
wrote:

>See what I mean now?

Yes, you are confusing subselects and inline views. Your 'subselects'
are inline views.


Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address