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

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:15:24 CDT