I would like to communicate a concept using very simple tables, I think this is the best way for me to explain the requirement....
--EMPLOYEES
CREATE TABLE EMPLOYEES (
ID NUMBER,
NAME VARCHAR2(20),
LOCATION VARCHAR2(30),
AMOUNT NUMBER
);
INSERT INTO EMPLOYEES VALUES (111,'ANA','LONDON',100);
INSERT INTO EMPLOYEES VALUES (111,'ANA','LONDON',200);
INSERT INTO EMPLOYEES VALUES (222,'JAKE','NEW YORK',700);
INSERT INTO EMPLOYEES VALUES (333,'SAM','TOKYO',300);
INSERT INTO EMPLOYEES VALUES (333,'SAM','TOKYO',600);
INSERT INTO EMPLOYEES VALUES (333,'SAM','TOKYO',900);
INSERT INTO EMPLOYEES VALUES (444,'SURI','NEW DELHI',500);
INSERT INTO EMPLOYEES VALUES (555,'NIREV','MOSCOW',120);
INSERT INTO EMPLOYEES VALUES (555,'NIREV','MOSCOW',130);
INSERT INTO EMPLOYEES VALUES (555,'NIREV','MOSCOW',140);
COMMIT;
ID NAME LOCATION AMOUNT
---------------------------------------------------------------
111 ANA LONDON 100
111 ANA LONDON 200
222 JAKE NEW YORK 700
333 SAM TOKYO 300
333 SAM TOKYO 600
333 SAM TOKYO 900
444 SURI NEW DELHI 500
555 NIREV MOSCOW 120
555 NIREV MOSCOW 130
555 NIREV MOSCOW 140
--GOLD_MEMBER
CREATE TABLE GOLD_MEMBER
(
ID NUMBER,
GOLD_CODE VARCHAR2(5)
);
INSERT INTO GOLD_MEMBER VALUES (111, 'ALO');
INSERT INTO GOLD_MEMBER VALUES (333, 'STO');
INSERT INTO GOLD_MEMBER VALUES (555, 'NMO');
COMMIT;
ID GOLD_CODE
--------------------------
111 ALO
333 STO
555 NMO
--GOLD_LIST
CREATE TABLE GOLD_LIST
(
ID NUMBER,
GOLD_CODE VARCHAR2(5),
TOTAL_AMOUNT NUMBER
);
--RESULT_SET
SELECT * FROM GOLD_LIST;
ID GOLD_CODE TOTAL_AMOUNT
----------------------------------------------
111 ALO 300
333 STO 1800
555 NMO 390
In order to arrive at the 'RESULT_SET', the simple SQL code is:
INSERT INTO GOLD_LIST
SELECT EMP.ID, GOLD.GOLD_CODE, SUM(EMP.AMOUNT)
FROM EMPLOYEES EMP JOIN GOLD_MEMBER GOLD
ON (EMP.ID = GOLD.ID)
GROUP BY EMP.ID, GOLD.GOLD_CODE;
The requirement established was:
Process only one EMPLOYEE.ID at a time when it matches GOLD_MEMBER.ID .
Perform the required sum amount calculation then insert into GOLD_LIST table.
Once the EMPLOYEE.ID has been processed, it should never be reprocessed.
Proceed to the next EMPLOYEE.ID (to the same process and so on...)
The requirement that cannot be ignored is "to process one ID only then insert to GOLD_LIST before you start processing the next ID'
The worry on performance issue about committing every row will be disregarded for this one only.
Any ideas?