| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Need help with calculating differences in row values using cursor
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;
>
>
 |
 |