I have a requirement where I need to iterate through 2 tables and insert the data into 3rd table but the issue is when I am iterating it is going into cartezian product and giving me lot of records which are not required.

for cg_cur in(select * from customer_ref_lookup)
loop
for sw_cur1 in (select * from stg_switch_data where customer_group=cg_cur.customer_group and field_a='1' order by row_no)
loop
for sw_cur2 in (select * from stg_switch_data where customer_group=sw_cur1.customer_group and group_id=sw_cur1.group_id and
field_a in('2','3','5','6') order by row_no)
loop

INSERT INTO soassetscmdetails(
)
select

FROM installsotypedetails ins,
customer_field_def cfd,
CONTACTGROUP cg,
scm_cust_grp cust

end loop;
end loop;
end loop;
end;
/

Hence I tried using Record Type so that I can take values from there.

CREATE OR replace PACKAGE load_so
AS
TYPE cust_def IS RECORD ( field_name customer_field_def.field_name%TYPE );

TYPE t_cust_def IS
TABLE OF cust_def INDEX BY BINARY_INTEGER;

custdef t_cust_def;

PROCEDURE ins_soassetcmdetails (
v_customer_id IN customer_field_def.customer_id%TYPE,
v_serviceprovider_id IN customer_field_def.serviceprovider_id%TYPE,
p_cust_field_rec IN VARCHAR2
);

CREATE OR REPLACE PACKAGE BODY load_so
AS PROCEDURE ins_soassetcmdetails (
v_customer_id IN customer_field_def.customer_id%TYPE,
v_serviceprovider_id IN customer_field_def.serviceprovider_id%TYPE

)
IS
BEGIN
select max(case......)field_1, max(case......)field_2, ....
FROM
customer_field_def cfd into custdef
WHERE
customer_id = v_customer_id
AND serviceprovider_id = v_serviceprovider_id
GROUP BY
customer_id;

for cg_cur in(select * from customer_ref_lookup)
loop
for sw_cur1 in (select * from stg_switch_data where customer_group=cg_cur.customer_group and field_a='1' order by row_no)
loop
for sw_cur2 in (select * from stg_switch_data where customer_group=sw_cur1.customer_group and group_id=sw_cur1.group_id and
field_a in('2','3','5','6') order by row_no)
loop

insert into table(columns)
select
custdef.field_1,
custdef.field_2...)

end;


I am still getting wrong records, can you please help me in this.

Step 1. iterate through customer_ref_lookup and check records -- take customer_group_name
Step 2. iterate through stg_switch_data and check records with some condition --
Step 3. take customer_id and customer_group_name iterate through installso table ,lookup with
customer_field_def and insert into soassetcmdetails.

SQL> create table t ( x int, y int, z int );

Table created.

SQL>
SQL> declare
  2    type rec is record ( x1 int, y1 int, z1 int );
  3    r rec;
  4  begin
  5    r.x1 := 10; r.y1 := 20; r.z1 := 30;
  6    insert into t values r;
  7
  8    for i in ( select 1 p1, 2 p2, 3 p3 from dual )
  9    loop
 10       insert into t values i;
 11    end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t;

         X          Y          Z
---------- ---------- ----------
        10         20         30
         1          2          3

SQL> declare
  2    type rec is record ( x1 int, y1 int, z1 int );
  3    r rec;
  4  begin
  5    r.x1 := 10; r.y1 := 20; r.z1 := 30;
  6    insert into t select r.x1, r.y1, r.z1 from dual;
  7  end;
  8  /

PL/SQL procedure successfully completed.