inserting rows in cursor and breaking up when effect date matches
Hi
I have created a table cadreinc (empno varchar2(8), pay number(5), da number(5) , scale varchar2(11), effectdate date , flag varchar2(1)) with values as shown below.
04485816 12800 4200 09300-34800 1-Jan-2006 Y
04485816 13000 4200 09300-34800 1-Jul-2006 Y
04485816 13450 4600 09300-34800 27-Sep-2006 Y
04485816 13675 4600 09300-34800 1-Jul-2007 Y
04485816 14200 4600 09300-34800 1-Jul-2008 Y
04485816 14650 4600 09300-34800 1-Jul-2009 Y
I just want to manipulate data into another table by using this data and i want the result as
04485816 12800 4200 09300-34800 01-jan-2006
04485816 12800 4200 09300-34800 01-feb-2006
.
.
.
04485816 13000 4200 09300-34800 1-Jul-2006
04485816 13000 4200 09300-34800 1-aug-2006
.
.
.
like data reproduced on every month and get changes in value based on the effect date.
how to write pl sql program for this??
I have tried some what like
DECLARE
EMP VARCHAR2(8);
PAY NUMBER(5);
GP NUMBER(5);
SCALE VARCHAR2(11);
INC date;
PAD VARCHAR2(11);
FL VARCHAR2(1);
CURSOR A IS SELECT EMPNO,VIPAY,GRADEPAY,VISCALE,INCDATE,FLAG FROM CADREINC;
BEGIN
<<i_loop>> FOR I IN A LOOP
INC:=I.INCDATE;
FL:='Y';
<<j_loop>> FOR K IN 1..12 LOOP
INSERT INTO CADREFIX ( EMPNO,VIPAY,GRADEPAY,VISCALE,INCDATE) VALUES
(I.EMPNO,I.VIPAY,I.GRADEPAY,I.VISCALE,I.INCDATE);
I.incdate:=LAST_DAY(I.INCDATE)+1;
EXIT j_loop WHEN pay>i.vipay and inc=i.incdate and fl='Y';
--EXIT WHEN INC='01-JUL-2010';
END LOOP;
-- i.INCdate:=LAST_DAY(I.INCDATE)+1;
EXIT i_loop WHEN pay>i.vipay and inc=i.incdate and fl='Y' ;
END LOOP;
COMMIT;
END;
but unable to get the desired results. The pay comes for every 12 months instead of upto effect date.
Please help.