-
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:
Code:
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?
-
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?
-
Horrible Oracle SQL syntax? I'd say it's nice but you misuse it horribly.
Look at this:
Code:
select dept.deptno,
(select count(*) from emp where emp.deptno=dept.deptno) TheCount
from dept;
DEPTNO THECOUNT
---------- ----------
10 2
20 5
30 5
40 2
25 0
This is an example of using correlated subquery. Works fine, counts employees for each department. The drawback is that it need to execute 6 queries instead of just one. If I'd write such query, I'd get rid of subquery:
Code:
select dept.deptno, count(emp.deptno)
from emp, dept
where emp.deptno(+)=dept.deptno group by dept.deptno;
DEPTNO COUNT(EMP.DEPTNO)
---------- -----------------
10 2
20 5
25 0
30 5
40 2
One query, same results, readable code. Rewrite the query, do not use correlated subquery unless you really have to.
I mean something like that, put outer joins where appropriate:
Code:
SELECT tableOne.name, tableTwo.nameAgain,
MIN(tableThree.createDate) oldestEvent,
SUM(COUNT(DISTINCT tableFive.specialID)) numOfEncounters
FROM tableOne, tableTwo ,tableThree, tableFour, tableFive, tableSix
WHERE tableOne.statusID = tableTwo.statusID
AND tableTwo.nameType = 'Status_Open'
AND tableOne.name != 'system_account'
AND tableThree.tableFourID = tableFour.tableFourID
AND tableFour.nameType = 'Historical_Event'
AND tableThree.tableTwoID = tableOne.tableTwoID
AND tableFive.tableSixID = tableSix.tableSixID
AND tableSix.nameType = 'Open_Encounter'
AND tableFive.tableFiveID = tableOne.tableFiveID
GROUP BY tableOne.name, tableTwo.nameAgain
ORDER BY tableOne.loginState DESC, tableOne.name
Correlated subquery might be real performnace killer - just this week I came accross a similar query which had two-level correlated subquery. Executing on tables about 10,000 records took 20+ minutes. After rewriting in manner I suggested above, less than one second.
Ales The whole difference between a little boy and an adult man is the price of toys
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|