SELECT (SELECT) FROM ... in PL/SQL

From: Bosco Ng <boscong_at_leccotech.com>
Date: Wed, 11 Jun 2003 20:52:59 +0800
Message-ID: <3ee72510$1@shknews01>

I have the following query:

SELECT Table_1.key_1,

       NVL((SELECT 'Y'
            FROM   Table_2,
                   Table_3
            WHERE  Table_2.Key_1 = Table1.Key_1
     AND    Table_2.Key_2 = 'G'
     AND    Table_3.Key_3 = Table2.Key_3
     AND    SUBSTR(Table_3.Key_4, 1, 3) NOT IN ('111','222','333','444')
     AND    Table_3.Key_5 = 'E'
            AND    ADD_MONTHS(TRUNC(SYSDATE,'MONTH'),-1) BETWEEN
Table_3.Key_6 AND Table_3.Key_7 - 1
     AND    Table_3.Key_8 = (SELECT MAX(Table4.Key_8)
                             FROM   Table_3 Table_4
        WHERE  Table4.Key_3 = Table3.Key_3
        AND    Table_4.Key_4 = Table4.Key_4)
           ), 'N') Indicator_Code,

FROM Table_1

basically the idea is to retrieve a code, either 'Y' or 'N' each of the value of Key_1 column of Table_1.

This code is retrieved based on the presence of a particular type of record in Table_2 and Table_3 within a particular range of dates

i.e. if for a particular value of Key_1 of Table_1 that no corresponding record exist in Table_2 and Table_3, then the code should be 'N', otherwise it is 'Y'

OK, the query above achieve pretty good performance. Table_1 is just 20 records
whereas Table_2 is 150K rows and Table_3 is 500K rows. (in production, all 3 will be able 10 times bigger). The query now returns in under 1 second because
for each record in Table_1, it can do a unique index search on Table_2 and Table_3

But now I have a problem, I have to put this query into a pl/sql package, and my
version is 8.1.7 and it does not permit such SELECT .. (SELECT) syntax, so I have to
resort to other ways with no good results.

I've tried this:

SELECT Table_1.Key_1,

       Fn_Indicator_Code(Table_1.Key_1)
FROM Table_1.Key_1

In the Fn_Indicator_Code, it the similar SELECT statement like that within NVL() above. But since for each record will invoke a context switch and a distinct SQL firing, it becomes pretty slow!

Can anyone suggest me of a good way to handle this?? Received on Wed Jun 11 2003 - 07:52:59 CDT