Help , how to transfer data and processing 1

[oraprog]

Following is the scenario:

create table test1
( emp_no number,
p_no varchar2(2),
p_amount number(10),
)

insert into test1 values (1, 'x', 10 );
insert into test1 values (1, 'x', 20 );

insert into test1 values (1, 'y', 35 );

insert into test1 values (1, 'z', 60 );
insert into test1 values (1, 'z', 40 );

insert into test1 values (1, 'w', 90 );

insert into test1 values (2, 'x', 50 );
insert into test1 values (2, 'w', 70 );
insert into test1 values (2, 'w', 40 );



create table test2
( emp_no number,
e_date date,
serial_no number, -- EVERY INSERT RECORD +1 SAME EMP_NO
p_no_amount_x varchar2(10),
p_no_amount_y varchar2(10),
p_no_amount_z varchar2(10),
p_no_amount_w varchar2(10),
primary_key(emp_no ,e_date, serial_no)
)

I want to insert the values ??in test1(p_no) to test2
We link mtach sum value p_amount for test1(p_no) and if find with value column test2

For example


emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2002-01-30 1 30 35 100 90
2 2002-01-30 1 50 110
------------------
i CREATE ::::

CREATE OR REPLACE PROCEDURE p_insert ( P_DATE IN DATE)
IS
V_EMP_NO test1.EMP_NO%TYPE;
V_p_no test1.p_no%TYPE;
V_p_amount test1.P_AMOUNT%TYPE;
V_p_no_amount_x test2.p_no_amount_x%TYPE;
V_p_no_amount_Y test2.p_no_amount_Y%TYPE;
V_p_no_amount_Z test2.p_no_amount_Z%TYPE;
V_p_no_amount_W test2.p_no_amount_W%TYPE;

CURSOR EMP_CUR IS
SELECT EMP_NO,p_no,SUM(P_AMOUNT)
FROM test1
GROUP BY EMP_NO,P_NO
ORDER BY EMP_NO,P_NO ;

V_SERIAL_NO number;
BEGIN

OPEN EMP_CUR;
LOOP
FETCH EMP_CUR INTO V_EMP_NO,V_p_no,V_p_amount;
EXIT WHEN EMP_CUR%NOTFOUND;

-----------------------------
IF V_p_no = 'x' THEN
V_p_no_amount_x := V_p_amount;
END IF;
IF V_p_no = 'y' THEN
V_p_no_amount_Y := V_p_amount;
END IF;
IF V_p_no = 'w' THEN
V_p_no_amount_Y := V_p_amount;
END IF;
IF V_p_no = 'z' THEN
V_p_no_amount_W := V_p_amount;
END IF;

BEGIN
SELECT NVL(MAX(SERIAL_NO),0)+1
INTO V_SERIAL_NO
FROM TEST2
WHERE EMP_NO = V_EMP_NO;
END;
-----------------------------
INSERT INTO TEST2( EMP_NO,e_date,serial_no ,p_no_amount_x,p_no_amount_Y,p_no_amount_Z,p_no_amount_W)
VALUES( V_EMP_NO, P_DATE , V_SERIAL_NO ,V_p_no_amount_x,V_p_no_amount_Y,V_p_no_amount_Z,V_p_no_amount_W );
-----------------------------

END LOOP;


CLOSE EMP_CUR;

COMMIT;
END;
/
-------
I EXECUTE : select p_insert(to_date('2002-01-30', 'yyyy-mm-dd')) from dual


AND RESULT :::

emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2002-01-30 1 null 90 null null
1 2002-01-30 1 30 90 null null
1 2002-01-30 1 30 35 null null
1 2002-01-30 1 30 35 null 100

2 2002-01-30 1 30 150 null 100
2 2002-01-30 2 50 150 null 100


This result is not as I want,,,


I Want like this ::

emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2002-01-30 1 30 35 100 90
2 2002-01-30 1 50 null null 110

and another EXECUTE : select p_insert(to_date('2003-12-10', 'yyyy-mm-dd')) from dual


emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2003-12-10 2 30 35 100 90
2 2003-12-10 2 50 null null 110




then store in table test2 ::

emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2002-01-30 1 30 35 100 90
2 2002-01-30 1 50 null null 110
1 2003-12-10 1 30 35 100 90
2 2003-12-10 1 50 null null 110

Thanks a lot

 

[oraprog]

then store in table test2 ::

emp_no e_date serial_no p_no_amount_x p_no_amount_y p_no_amount_z p_no_amount_w
====== ====== ======= =========== ========= ========== ========
1 2002-01-30 1 30 35 100 90
2 2002-01-30 1 50 null null 110
1 2003-12-10 2 30 35 100 90
2 2003-12-10 2 50 null null 110

 

[Dagon]

I'm not sure why you have posted this twice. I've red-flagged the other one as a duplicate, so it might get removed.

I'm not entirely clear what the purpose of the date is. Is the idea that you are going to be inserting different data at different times and the date is provided as a way of distinguishing between the loads? At the moment, you seem to be inserting exactly the same data with a different date each time, which seems a bit odd.

I think you just need something like this:

create or replace procedure ins_test (p_date in DATE) is
begin 
 insert into test2 
 (emp_no, e_date, serial_no,
  p_no_amount_x,
  p_no_amount_y,
  p_no_amount_z,
  p_no_amount_w)
 select emp_no, p_date,  latest_serial_no+1,
 p_no_amount_x,
  p_no_amount_y,
  p_no_amount_z,
  p_no_amount_w
 from
 (select
 t1.emp_no,
 nvl(t2.latest_serial_no,0) as latest_serial_no,
 sum(case when t1.p_no = 'x' then p_amount end) p_no_amount_x,
 sum(case when t1.p_no = 'y' then p_amount end) p_no_amount_y,
 sum(case when t1.p_no = 'z' then p_amount end) p_no_amount_z ,
 sum(case when t1.p_no = 'w' then p_amount end) p_no_amount_w
 from test1 t1, 
     (select emp_no, max(serial_no) as latest_serial_no from test2 t2 group by emp_no) t2
 where t1.emp_no = t2.emp_no(+)
 group by t1.emp_no, t2.latest_serial_no);
end;
/

begin
  ins_test(to_date('2002-01-30', 'YYYY-MM-DD'));
  ins_test(to_date('2003-12-10', 'YYYY-MM-DD'));
end;

For Oracle-related work, contact me through Linked-In.

 

[oraprog]

Tahnks a lot Dagon