Loop through each record from a table and a calculated record into other table in Oracle?

My oracle is rusty, but it should be something like this... When dealing with sql, you want to think in sets and not row by row. You can comment the first line and run it to preview the data that will be inserted into your table INSERT INTO INCOME_DETAILS(INCOME_ID, ITEM_ID, ITEM_VALUE) SELECT INCOME_MASTER. INCOME_ID, 205, b+c+d-a FROM INCOME_MASTER INNER JOIN ( SELECT INCOME_MASTER.

INCOME_ID, SUM(DECODE(ITEM_ID, 201, ITEM_VALUE, 0)) a, SUM(DECODE(ITEM_ID, 202, ITEM_VALUE, 0)) b, SUM(DECODE(ITEM_ID, 203, ITEM_VALUE, 0)) c, SUM(DECODE(ITEM_ID, 204, ITEM_VALUE, 0)) d FROM INCOME_MASTER INNER JOIN INCOME_DETAILS ON INCOME_MASTER. INCOME_ID = INCOME_DETAILS. INCOME_ID WHERE INCOME_DETAILS.

ITEM_ID IN (201,202,203,204) GROUP BY INCOME_MASTER. INCOME_ID ) details ON INCOME_MASTER. INCOME_ID = details.

INCOME_ID.

My oracle is rusty, but it should be something like this... When dealing with sql, you want to think in sets and not row by row. You can comment the first line and run it to preview the data that will be inserted into your table. INSERT INTO INCOME_DETAILS(INCOME_ID, ITEM_ID, ITEM_VALUE) SELECT INCOME_MASTER.

INCOME_ID, 205, b+c+d-a FROM INCOME_MASTER INNER JOIN ( SELECT INCOME_MASTER. INCOME_ID, SUM(DECODE(ITEM_ID, 201, ITEM_VALUE, 0)) a, SUM(DECODE(ITEM_ID, 202, ITEM_VALUE, 0)) b, SUM(DECODE(ITEM_ID, 203, ITEM_VALUE, 0)) c, SUM(DECODE(ITEM_ID, 204, ITEM_VALUE, 0)) d FROM INCOME_MASTER INNER JOIN INCOME_DETAILS ON INCOME_MASTER. INCOME_ID = INCOME_DETAILS.

INCOME_ID WHERE INCOME_DETAILS. ITEM_ID IN (201,202,203,204) GROUP BY INCOME_MASTER. INCOME_ID ) details ON INCOME_MASTER.

INCOME_ID = details. INCOME_ID.

Thanks a lot for a help. The values for b, c can be negative values but they are showing as 0. Please help – acadia Oct 23 '10 at 13:47 clyc, Could you please help?

The be and C values can be negative values but they are showing as zeros as we are going with MAX – acadia Oct 24 '10 at 11:52 1 Instead of Max, try using SUM – clyc Oct 24 '10 at 23:44 +1 SUM does fix this (in addition to dropping the comma on line 10). – Leigh Riffel Oct 25 '10 at 20:51 fixed typos and changed max to sum. Thanks for catching the extra "," leigh :) – clyc Oct 25 '10 at 20:59.

I agree with clyc, you need to think in sets. Unless we are missing details, this operation does not need any procedural code. Sample Data: create table income_master ( income_id number(10), income_type varchar2(10), income_date date ); create table income_details ( income_details_seq_no number(10), income_id number(10), item_id number(10), item_value number(10,2) ); insert into income_master values (1,'a',sysdate); insert into income_master values (2,'b',sysdate); insert into income_master values (3,'c',sysdate); insert into income_master values (4,'d',sysdate); insert into income_details values(1,1,201,1); insert into income_details values(2,1,202,20); insert into income_details values(3,1,203,300); insert into income_details values(4,1,204,4000); insert into income_details values(5,2,201,1); insert into income_details values(6,2,202,20); insert into income_details values(7,2,203,300); insert into income_details values(8,3,201,10); insert into income_details values(9,3,202,20); insert into income_details values(10,3,203,30); insert into income_details values(11,4,202,-90); insert into income_details values(12,4,202,-10); Insert Statement: INSERT INTO Income_Details (Income_Id, Item_Id, Item_Value) ( SELECT ma.

Income_Id, 205, SUM(DECODE(de. Item_ID,201,-1,1) * de. Item_Value) FROM Income_Master ma JOIN Income_Details de ON ma.

Income_Id = de. Income_Id AND de. Item_Id BETWEEN 201 and 204 GROUP BY ma.

Income_Id, 205 ); Results: select Income_Id, Item_Id, Item_Value from income_details WHERE Item_Id = 205; INCOME_ID ITEM_ID ITEM_VALUE ---------------------- ---------------------- ---------------------- 3 205 40 1 205 4319 2 205 319 4 205 -100.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.