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

From: Pete's <empete2000_at_yahoo.com>
Date: 12 Jun 2003 05:38:19 -0700
Message-ID: <6724a51f.0306120438.1150c37f@posting.google.com>

Try moving your subselect into the from clause. i.e. like the following:

select table1.key1

       nvl(dynamictable1.indicator_code,'N')   from TABLE1,

       (select 'Y',table2.key1
          from table2,table3,table4
         where .......
       ) dynamictable1

 where table1.key1 = dynamictable1.key1
;

You may have to outer join the table1.key1 = dynamictable1.key1(+)

Just an idea to try, havent tested.
HTH
Pete's

The views I may have expressed here are solely my own and not that of my employer.

"Bosco Ng" <boscong_at_leccotech.com> wrote in message news:<3ee72510$1_at_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 Thu Jun 12 2003 - 07:38:19 CDT