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

Home -> Community -> Usenet -> c.d.o.server -> Re: Remove rows from a cursor?

Re: Remove rows from a cursor?

From: Vince <vinnyop_at_yahoo.com>
Date: 5 Jul 2002 08:03:15 -0700
Message-ID: <56e2f55a.0207050703.1bc34529@posting.google.com>


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

Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<Xns923FE17D82FE8gnuegischgnueg_at_130.133.1.4>...
> Not sure, what exactly you want, but maybe the following helps:
>
> set feedback off
> set pages 5000
>
> alter session set nls_date_format = 'dd.mm.yyyy';
>
> create table prod_price (
> product varchar2(10),
> price number(5,2),
> thru date
> );
>
>
>
> insert into prod_price values
> ('A', 0.00, to_date('01.01.0001','dd.mm.yyyy'));
> insert into prod_price values
> ('A', 0.10, to_date('28.12.2001','dd.mm.yyyy'));
> insert into prod_price values
> ('A', 0.10, to_date('15.01.2002','dd.mm.yyyy'));
> insert into prod_price values
> ('A', 0.10, to_date('25.01.2002','dd.mm.yyyy'));
> insert into prod_price values
> ('A', 0.15, to_date('20.03.2002','dd.mm.yyyy'));
> insert into prod_price values
> ('A', 0.20, to_date('30.04.2002','dd.mm.yyyy'));
> insert into prod_price values
> ('A', 0.00, to_date('31.12.9999','dd.mm.yyyy'));
>
>
> select date_from, date_thru,price from (
> select lag(thru,1) over (order by thru) date_from, thru date_thru, price
> from
> prod_price
> where product = 'A')
> where
> date_from between to_date('01.01.2002') and to_date('31.01.2002')
> or
> date_thru between to_date('01.01.2002') and to_date('31.01.2002');
>
>
> drop table prod_price;
>
>
> Rene
>
>
>
>
> > I have a table that contains product prices and end dates as in:
> > 1) product: A price: .10 thru: Jan 15
> > 2) product: A price: .15 thru: Mar 20
> > 3) product: A price: .20 thru: Apr 30
> >
> > I want to select all rows that contain prices for January. In this
> > case, row 1 contains the price for Jan 1 to Jan 15. Row 2 contains
> > the price for the remainder of January.
> >
> > It seems the most efficient way to do this is to select all rows
> > where THRU >= Jan 1 and then loop through the rows in a cursor
> > and decide which rows are needed. My question is, how can I delete
> > rows from the cursor that I don't need or create a new cursor of
> > just the rows I want?
> >
> > Thanks!
Received on Fri Jul 05 2002 - 10:03:15 CDT

Original text of this message

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