Reformat SQL Syntax to for a correlated SQL query? - 07-15-2003 , 08:32 AM
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'))
(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 =
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',
But my tableOne 'outer query' reference is now two levels deep and
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
Re: Reformat SQL Syntax to for a correlated SQL query? - 07-15-2003 , 11:19 AM
Originally posted by Robert Schultz
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
Re: Reformat SQL Syntax to for a correlated SQL query? - 07-17-2003 , 12:41 PM
andrewst <member14183 (AT) dbforums (DOT) com> wrote
Maybe I should simplify it better:
I'm doing something like this:
select e.ename, (select count(*) from dept where dep.deptno =
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?
Re: Reformat SQL Syntax to for a correlated SQL query? - 07-17-2003 , 01:20 PM
On 17 Jul 2003 10:41:10 -0700, sembiance (AT) hotmail (DOT) com (Robert Schultz)
are inline views.
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address