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: Need help with calculating differences in row values using cursor

Re: Need help with calculating differences in row values using cursor

From: omar_mpls <omar_mpls_at_hotmail.com>
Date: 22 Aug 2006 17:26:02 -0700
Message-ID: <1156292762.547577.267860@75g2000cwc.googlegroups.com>


Thanks Mark for your quick response. Even though I'm trying to calculate the differences between two rows the logic changes at times. It depends on whether it's the only row for a particular serial_num or it's the 1st row of a particular serial_num. Here is the requested version information...

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.4.0 - Production NLSRTL Version 3.4.1.0.0 - Production

Mark D Powell wrote:
> omar_mpls wrote:
> > CREATE TABLE Error
> > ( Serial_Num VARCHAR2(10),
> > Run_Hrs NUMBER(8) NOT NULL,
> > Error_Cnt NUMBER(4)
> > );
> >
> > INSERT INTO Error VALUES('A', 10, 2);
> > INSERT INTO Error VALUES('B', 10, 5);
> > INSERT INTO Error VALUES('B', 20, 7);
> > INSERT INTO Error VALUES('B', 30, 7);
> > INSERT INTO Error VALUES('B', 40, 10);
> > INSERT INTO Error VALUES('C', 20, 3);
> > INSERT INTO Error VALUES('C', 50, 5);
> > INSERT INTO Error VALUES('C', 100, 5);
> >
> > Select * from Error
> >
> > SERIAL_NUM RUN_HRS ERROR_CNT
> > A 10 2
> > B 10 5
> > B 20 7
> > B 30 7
> > B 40 10
> > C 20 3
> > C 50 5
> > C 100 5
> >
> > I would like to add the differences (new errors) for each row and each
> > Serial_Num.
> > My goal is to create the following result set using cursor.
> >
> > SERIAL_NUM RUN_HRS ERROR_CNT NEW_ERRORS
> > A 10 2 2
> > B 10 5 5
> > B 20 7 2
> > B 30 7 0
> > B 40 10 3
> > C 20 3 3
> > C 50 5 2
> > C 100 5 0
> >
> > /******************************************
> > This is what I wrote to get the desired result set and obviuosly there
> > are some
> > errors in there. Threfore I was not able to get the result set. If any
> > one would
> > be kind enough to review my code and correct it so that I can get the
> > desired result
> > set would be very much apprecited.
> > *******************************************/
> >
> > SET SERVEROUTPUT ON;
> >
> > Declare
> > cur_serial_num error.serial_num%TYPE := '';
> > cur_error_count error.error_cnt%TYPE := 0;
> > new_errors error.error_cnt%TYPE := 0;
> >
> > BEGIN
> > FOR error_rec IN (select * from error order by serial_num, run_hrs)
> > LOOP
> > IF error_rec.serial_num = cur_serial_num
> > THEN
> > new_errors := (error_rec.error_cnt - cur_error_count);
> > ELSE
> > new_errors := error_rec.error_cnt;
> > END IF;
> > cur_error_count := error_rec.error_cnt;
> > cur_serial_num := error_rec.serial_num;
> > DBMS_OUTPUT.PUT_LINE(error_rec.serial_num || '-' || error_rec.run_hrs
> > || '-' || error_rec.error_cnt || '-' || new_errors );
> > END LOOP;
> > END;

>

> Unfortunately you did not specify your edition and version of Oracle so
> this may not be applicable, but look up the analytic function: log in
> the SQL manual. It allows you to compare the current row to the
> previous row and do the math (current - previous = new).
>

> I would try to post an example but I do not have an Oracle system in
> front of me.
>
> HTH -- Mark D Powell --
Received on Tue Aug 22 2006 - 19:26:02 CDT

Original text of this message

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