next minus previous

Dev Shed Forums Sponsor:

SlickEdit: Code in over 40 languages across 7 platforms. SlickEdit’s unmatched power, speed, and flexibility allows even the most accomplished developers to write better code faster. Download a free trial today!

January 25th, 2005, 12:54 AM

yandolce

Contributing User

Join Date: Aug 2004

Posts: 119

Time spent in forums: 17 h 6 m 41 sec
Reputation Power: 4

next minus previous

i have a database column which looks like this:

0
0
1
2
4
5
9
12

and wants it to become like this:
0
0
1
1
2
1
4
3

which means the next row of the column will minus the previous row. can sql update statement do this?

January 25th, 2005, 10:11 AM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,762

Time spent in forums: 2 Weeks 4 Days 4 h 29 m 45 sec
Reputation Power: 37

There is no inherant concept of next or previous in a RDBMS.

You need to define what you mean by previous based on column values and use that in an update statement like

Code:

update t set v = v - (select v from t
where <criteriaForDeterminingPreviousRow>)

January 25th, 2005, 10:59 AM

jim mcnamara

......@.........

Join Date: Jun 2004

Posts: 1,307

Time spent in forums: 1 Week 3 Days 4 h 28 m 57 sec
Reputation Power: 48

As swampboogie mentioned this doesn't make a lot of sense
Assuming the table is sequential by ROWID try something (untested) like this

Code:

DECLARE
   this_row ROWID;
   previous_row ROWID;
   this_value NUMBER(10):=0;
   previous_value NUMBER(10):=0;
   TYPE table_rec IS RECORD (
           mytable.fld1%TYPE;
           mytable.ROWID ROWID;
   );
   TYPE my_records IS TABLE OF table_rec INDEX BY BINARY_INTEGER;
   i NUMBER(5):=0;
   
   CURSOR current
   is
   SELECT fld1,ROWID from mytable
   ORDER BY ROWID;
   
   CURSOR lastone 
   is
   SELECT fld1,ROWID 
   from mytable 
   ORDER BY ROWID;
   

BEGIN
   open current;
   open lastone;
   FETCH current into this_value, this_row;
   LOOP
        i:=i+1;
        FETCH current into my_records(i);
        EXIT WHEN current%NOTFOUND;
        FETCH lastone into previous_value,previous_row;
        my_records(i).fld1=myrecords(i).fld1-previous_row;
   END LOOP;
   CLOSE current;
   CLOSE lastone;
   FOR X in my_records.FIRST .. my_records.LAST
   LOOP
       UPDATE mytable 
       set fld1=my_records(x).fld1
       WHERE ROWID=my_records(x).ROWID;
       
   
   END LOOP;
   COMMIT;   


END;
/

February 1st, 2005, 09:39 AM

KK2796

Registered User

Join Date: Jul 2004

Posts: 11

Time spent in forums: 50 m 40 sec
Reputation Power: 0

Code:

SQL> INSERT INTO T1 VALUES (0);

1 row created.

SQL> INSERT INTO T1 VALUES (0);

1 row created.

SQL> INSERT INTO T1 VALUES (1);

1 row created.

SQL> INSERT INTO T1 VALUES (2);

1 row created.

SQL> INSERT INTO T1 VALUES (4);

1 row created.

SQL> INSERT INTO T1 VALUES (5);

1 row created.

SQL> INSERT INTO T1 VALUES (9);

1 row created.

SQL> INSERT INTO T1 VALUES (12);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM T1;

       C1
---------
        0
        0
        1
        2
        4
        5
        9
       12

8 rows selected.

SQL> SELECT C1 - NVL(LAG(C1) OVER (ORDER BY C1),0) RESULT
  2  FROM T1;

   RESULT
---------
        0
        0
        1
        1
        2
        1
        4
        3

8 rows selected.

SQL>

Edit: oops, just saw you wanted an update statement...

Code:

UPDATE T1 O SET C1 = 
    (SELECT RESULT FROM 
        (SELECT (C1 - NVL(LAG(C1) OVER (ORDER BY C1),0)) RESULT, 
                ROWID RID  
         FROM T1 I  
        ) I  
      WHERE I.RID = O.ROWID
    );

(updating w/ analytic functions can be a bit tricky)

Comments on this post

pabloj agrees!

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: