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

Question

I am new to Oracle 10g. I have couple of tables as shown below:

INCOME_MASTER
INCOME_ID     NUMBER(10) sEQ NUMBER,
INCOME_TYPE   VARCHAR2(10),
INCOME_DATE   DATE

INCOME_DETAILS
INCOME_DETAILS_SEQ_NO     NUMBER(10) SEQUENCE,
INCOME_ID                 NUMBER(10),
ITEM_ID                   NUMBER(10),
ITEM_VALUE                NUMBER (10,2)

I have lot of values in both the tables.

I need to query all the records from the INCOME_MASTER table and for each each record in INCOME_MASTER table, I need to loop through

SELECT INCOME_ID FROM INCOME_MASTER
select ITEM_VALUE FROM INCOME_DETAILS WHERE INCOME_ID= FROM above query and ITEM_ID=201
select ITEM_VALUE FROM INCOME_DETAILS WHERE INCOME_ID= FROM above query and ITEM_ID=202
select ITEM_VALUE FROM INCOME_DETAILS WHERE INCOME_ID= FROM above query and ITEM_ID=203
select ITEM_VALUE FROM INCOME_DETAILS WHERE INCOME_ID= FROM above query and ITEM_ID=204

I need to add values from ITEM_ID 202,203,204 and deduct the value from ITEM_ID=201 value

whatever value I get I need to insert a new record into INCOME_DETAILS

insert into INCOME_DETAILS 
  values(INCOME_ID,205,value from above)

I want to do this for all the records in Income_master table

Answer 1

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;

Answer 2

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