Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL problem under Oracle 7.3.2

Re: PL/SQL problem under Oracle 7.3.2

From: victor <victor_at_PERCOMBANK.KIEV.UA>
Date: 20 Jan 1999 14:45:19 GMT
Message-ID: <01be4483$3b517030$cd14abcc@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 - 08:45:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US