| 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
"omar_mpls" <omar_mpls_at_hotmail.com> a écrit dans le message de news: 1156291611.464758.303260_at_74g2000cwt.googlegroups.com...
| 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;
|
SQL> select * from error order by serial_num, run_hrs;
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
8 rows selected.
SQL>
SQL> select serial_num, run_hrs,
2 error_cnt - 3 nvl(lag(error_cnt) over (partition by serial_num order by run_hrs),0) 4 new_errors
A 10 2 B 10 5 B 20 2 B 30 0 B 40 3 C 20 3 C 50 2 C 100 0
8 rows selected.
Regards
Michel Cadot
Received on Tue Aug 22 2006 - 23:47:13 CDT
 |
 |