Re: PL/SQL problem under Oracle 7.3.2

Hi Victor

Thanks for your message, but could you clear up as to why it doesn't work as the PL/SQL manual says it is OK to have subqueries in the from clause. Am I doing something which is inherently unsafe or something.

 Thanks in advance and apologies for the poor formatting of the sql.

 Al

victor <victor_at_PERCOMBANK.KIEV.UA> wrote in message news:01be4483$3b517030$cd14abcc_at_victor...
>
>
>Alan Patil <Alan_Patil_at_msn.com> wrote in article
><egoMc2FR#GA.131_at_upnetnews05>...
>> When I run the following sql under sqlplus or toad (without the into) it
>> works fine but when I run it from a PLSQL proc it does not resolve the
>> column aliases correctly and the references to r.rcost and a.acost get
>> converted to :b1 and :b2 on the last two lines. Is there a work around
>for
>> this bug without creating a view based on this sql.
>>
>> select count(*) into loop_count2
>> from (select x.cumulated_id, sum(prebilled_cost) rcost
>> from bose.r_rated_transactions x,
>> (select distinct subscriber_id from
>> bose.temp_r_rated_transactions_arch) t,
>> (select distinct cumulated_id from
>> bose.pb_cumulated_archive) p
>> where t.subscriber_id = x.subscriber_id
>> and x.cumulated_id = p.cumulated_id
>> group by x.cumulated_id) r,
>> (select x1.cumulated_id, sum(prebilled_cost) acost
>> from bose.r_rated_transactions_arch x1,
>> (select distinct subscriber_id from
>> bose.temp_r_rated_transactions_arch) t1,
>> (select distinct cumulated_id from
>> bose.pb_cumulated_archive) p1
>> where t1.subscriber_id = x1.subscriber_id
>> and x1.cumulated_id = p1.cumulated_id
>> group by x1.cumulated_id) a,
>> bose.pb_cumulated_archive p
>> where p.cumulated_id = r.cumulated_id(+)
>> and p.cumulated_id = a.cumulated_id(+)
>> and p.total_amount_net <> (NVL(r.rcost,0) + NVL(a.acost,0))
>> and (NVL(r.rcost,0) + NVL(a.acost,0)) > 0
>
>NO YOU CAN NOT
> use this connstruction on pl-sql
> only with view or modulate this select
> with cursors
Received on Wed Jan 20 1999 - 10:39:38 CST