Reformat SQL Syntax to for a correlated SQL query?

From: Robert Schultz <sembiance_at_hotmail.com>
Date: 15 Jul 2003 06:32:36 -0700
Message-ID: <fd216888.0307150532.232ee213@posting.google.com>

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? Received on Tue Jul 15 2003 - 08:32:36 CDT