Re: Remove rows from a cursor?

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 5 Jul 2002 17:35:52 GMT
Message-ID: <Xns9242C77F63A61gnuegischgnueg@130.133.1.4>

> This is the first time I have seen the lag function. In theory this
> would help me crunch data, however, I tried using it in a cursor and
> it fails.
>
> I am trying to get the difference between the previous row and the
> current row's accum_amount, using 8.1.7:
>
> CURSOR sel_load_info IS
> SELECT column_01 AS acct_no,
> column_02 AS payer_code,
> column_03 AS display_order,
> column_05 AS dscr,
> column_06 AS units,
> column_07 AS rate,
> column_08 AS amount,
> column_09 AS accum_amt,
> column_09 -
> lag(column_09, 1, 0) over (ORDER BY column_01, column_03) AS
> net_amt
> FROM customer_upload;
>
> It gets the following compile error (highlighting the "(" after
> "over":
>
> PLS-00103: Encountered the symbol "(" when expecting one of the
> following:
> , from
>
> I know that I can fetch column_09 into variable and do the math after
> each fetch, but I wanted to try this approach first.
>
> Any ideas why this fails? Can the lag function not be used within a
> cursor? The documentation does not state.
>
> Thanks,
> Vince

Hello Vince

Most probably it fails because you're using 8i. In 8i only a subset of Oracle's 8i SQL Features are supported in PL/SQL, most noteably, the analytic functions are not yet supported. This changes in 9i.

What you want to do (*as*a*workaround*) until you have 9i:

set serveroutput on size 100000

create table customer_upload (

  column_01 varchar2(100),
  column_02 varchar2(100),
  column_03 varchar2(100),
  column_05 varchar2(100),
  column_06 varchar2(100),
  column_07 varchar2(100),
  column_08 varchar2(100),
  column_09 number

);

insert into customer_upload values ('a','b','c','e','f','g','h',100);
insert into customer_upload values ('A','B','C','E','F','G','H',200);
insert into customer_upload values ('*','x','u','x','w','l','h',150);
insert into customer_upload values ('Z','Y','Q','M','.','_','H', 50);

create or replace procedure eee as
  type cur_t is ref cursor;
  cur cur_t;

  v_acct_no varchar2(100);
  v_payer_code varchar2(100);
  v_display_order varchar2(100);
  v_dscr varchar2(100);
  v_units varchar2(100);
  v_rate varchar2(100);
  v_amount varchar2(100);
  v_accum_amt number;
  v_net_amt number;

begin
  open cur for 'SELECT column_01 AS acct_no,

         column_02 AS payer_code,
         column_03 AS display_order,
         column_05 AS dscr,
         column_06 AS units,
         column_07 AS rate,
         column_08 AS amount,
         column_09 AS accum_amt,
         column_09 -
         lag(column_09, 1, 0) over (ORDER BY column_01, column_03) AS

 net_amt
  FROM customer_upload';

  loop
    fetch cur into

       v_acct_no,
       v_payer_code,
       v_display_order ,
       v_dscr ,
       v_units ,
       v_rate ,
       v_amount ,
       v_accum_amt ,
       v_net_amt;

    exit when cur%notfound;
    dbms_output.put_line ('net_amt: ' || v_net_amt);   /* do something */
  end loop;
end;
/

Hope this helps

Rene

-- 
Recherchen im schweizerischen Handelsregister: 
http://www.adp-gmbh.ch/SwissCompanies/Search.php3

Received on Fri Jul 05 2002 - 12:35:52 CDT