Hi all,
create table xxc_cust_id(cust_id number);
insert into xxc_cust_id values(1)
create table xxc_child (id number,part varchar2(20),amount number);
insert into xxc_child values(1,'Processor Replaced',1500)
insert into xxc_child values(1,'Mouse Replaced',200)
insert into xxc_child values(6,'Mouse Replaced',200)
create table xxc_parent (id number,cust_id varchar2(10));
insert into xxc_parent values(1,1)
insert into xxc_parent values(6,1)
select * from xxc_parent
ID CUST_ID
--------- ----------
1 1
6 1
select * from xxc_child
ID PART AMOUNT
-------- -------------------- ----------
1 Processor Replaced 1500
1 Mouse Replaced 200
6 Mouse Replaced 200
select * from xxc_cust_id
CUST_ID
--------
1
I need
CUST_ID SUM(AMOUNT)
-------- ----------
1 1900
for insert data into the xxc_details table
CREATE OR REPLACE PROCEDURE XXC_SUM_DETAILS
IS
CURSOR c_1
IS
SELECT cust_id
FROM xxc_cust_id
ORDER BY cust_id;
CURSOR c1
IS
SELECT a.cust_id, SUM (amount) total_sum
FROM xxc_parent a, xxc_child b
WHERE a.id = b.id
AND cust_id = c_cust_id.cust_id
GROUP BY a.cust_id;
BEGIN
FOR c_cust_id IN c_1
LOOP
dbms_output.put_line('Enter');
FOR c_rec IN c1
LOOP
BEGIN
INSERT INTO xxc_details (part_id, total_amount)
VALUES (c_rec.cust_id, c_rec.total_sum);
COMMIT;
END;
END LOOP;
END LOOP;
END;
LINE/COL ERROR
-------- -----------------------------------------------------------------
10/9 PL/SQL: SQL Statement ignored
12/42 PL/SQL: ORA-00904: "C_CUST_ID"."CUST_ID": invalid identifier
21/13 PL/SQL: SQL Statement ignored
22/41 PLS-00364: loop index variable 'C_REC' use is invalid
22/47 PL/SQL: ORA-00984: column not allowed here