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 -> PL/SQL problem under Oracle 7.3.2

PL/SQL problem under Oracle 7.3.2

From: Alan Patil <Alan_Patil_at_msn.com>
Date: Wed, 20 Jan 1999 10:16:05 -0000
Message-ID: <egoMc2FR#GA.131@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
Received on Wed Jan 20 1999 - 04:16:05 CST

Original text of this message

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