Thanks Michael for your efficient code. It worked great!
Omar
Michel Cadot wrote:
> "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
> 5 from error
> 6 order by serial_num, run_hrs
> 7 /
> SERIAL_NUM RUN_HRS 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 Wed Aug 23 2006 - 10:32:48 CDT
