• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 17251
  • Last Modified:

nested cursor to insert data into another table

I am using oracle 10g, I have  a parent table Table 1, child table table 2 and a third table Table 3, where I want to insert the data into from Table 1
There is also data from the child table that needs to be populated in Table 3.
In short Table 3 has a flat structure of therealtional data in Table1 and Table 2

Hereis what I have so far. I open a cursor for the parent and then loop through the parent cursor.
I would like to open the child cursor and fetch upto 4 rows, and then based on the child cursor row count,populate the variables v_empid_1, v_credit1, and then use the values to insert into Table3
Not sure if I have the right syntax


CREATE OR REPLACE PROCEDURE db_LOAD(

P_FROMDATE        IN varchar
,P_TODATE         IN varchar  
,P_LEGAL          IN varchar
,P_ASSET          IN varchar
,P_CURRENCY       IN varchar
,P_PROCESSED_BY   IN varchar
,P_ROWS_INSERTED  OUT NUMBER )

 

AS

v_empid_1 varchar;
v_empid_2 varchar;
v_empid_3 varchar;
v_empid_4 varchar;
v_credit_1 number;
v_credit_2 number;
v_credit_3 number;
v_credit_4 number;

CURSOR
LOAD_DATA is
select
  rowid                   ,
  ID                         ,
 CREATED_BY       ,
 CODE        

            FROM
         TABLE1
        WHERE
            PROCESS_FLAG = 'N';

  BEGIN    

  dbms_output.put_line('From Date - '|| P_FROMDATE );
  dbms_output.put_line('To Date -  ' || P_TODATE);
  dbms_output.put_line('Legal Entity - ' || P_LEGAL);
  dbms_output.put_line('Asset Class - ' || P_ASSET);
  dbms_output.put_line('Currency - ' || P_CURRENCY);
  dbms_output.put_line('Processed By - ' || P_PROCESSED_BY);

 FOR LOAD_REC in LOAD_DATA
v_empid_1:= '';
v_empid_2:=  '';
v_empid_3:=  '';
v_empid_4:=  '';
v_credit_1:= 0;
v_credit_2:= 0;
v_credit_3:= 0;
v_credit_4:= 0;

LOOP
   CURSOR
        LOAD_CHILD IS
      select *
            from (select *
                       from TABLE2  
                       order by EMP_ID desc)
            where rownum < 5;
     FOR CHILD_REC IN LOAD_CHILD
     //NEED TO ADD CODE SO THAT WHEN THE CURSOR ROW IS N
    //POPULATE
   IF LOAD_CHILD.ROWCOUNt =1
         V_EMPID_1 = child_rec.emp_id;
         v_credir_1 = chil_rec.credit;
  elseif LOAD_CHILD.rowcount = 2
      V_EMPID_2 = child_rec.emp_id;
         v_credir_2 = chil_rec.credit;

 

 INSERT INTO TABLE3
 (
  LINE_SEQ          ,
  CREATED_BY        ,
  CODE ,
  EMPYID1           ,
  EMPYID2           ,
  EMPYID3           ,
  EMPYID4           ,
  CREDIT_AMT1       ,
  CREDIT_AMT2       ,
  CREDIT_AMT3       ,
  CREDIT_AMT4    
  )
VALUES
(         db.LINENUMBERSEQUENCE.NEXTVAL,
          LOAD_REC.CREATED_BY ,
          LOAD_REC.CODE ,
          v_empid_1,
          v_empid_2,
          v_empid_3,
          v_empid_4,
         v_credit_1,
         v_credit_2,
         v_credit_3,
         v_credit_4        
          );

 

 UPDATE TABLE1

  SET PROCESS_FLAG = 'Y'
  where rowid = LOAD_REC.ROWID;

 

 

0
countrymeister
Asked:
countrymeister
  • 4
  • 3
2 Solutions
 
Jinesh KamdarCommented:
I have put the CURSOR declaration for the CHILD in the DECLARE section and corrected the syntax at some other places. Not sure though what you are trying to achieve using ROWNUM.

CREATE OR REPLACE PROCEDURE db_LOAD(

P_FROMDATE        IN varchar
,P_TODATE         IN varchar  
,P_LEGAL          IN varchar
,P_ASSET          IN varchar
,P_CURRENCY       IN varchar
,P_PROCESSED_BY   IN varchar
,P_ROWS_INSERTED  OUT NUMBER )

 

AS

v_empid_1 varchar;
v_empid_2 varchar;
v_empid_3 varchar;
v_empid_4 varchar;
v_credit_1 number;
v_credit_2 number;
v_credit_3 number;
v_credit_4 number;

CURSOR
LOAD_DATA is
select
  rowid,
  ID,
 CREATED_BY,
 CODE        
FROM
         TABLE1
WHERE
            PROCESS_FLAG = 'N';

CURSOR
        LOAD_CHILD IS
      select *
            from (select *
                       from TABLE2  
                       order by EMP_ID desc)
            where rownum < 5;

  BEGIN    

  dbms_output.put_line('From Date - '|| P_FROMDATE );
  dbms_output.put_line('To Date -  ' || P_TODATE);
  dbms_output.put_line('Legal Entity - ' || P_LEGAL);
  dbms_output.put_line('Asset Class - ' || P_ASSET);
  dbms_output.put_line('Currency - ' || P_CURRENCY);
  dbms_output.put_line('Processed By - ' || P_PROCESSED_BY);

 FOR LOAD_REC in LOAD_DATA
v_empid_1:= '';
v_empid_2:=  '';
v_empid_3:=  '';
v_empid_4:=  '';
v_credit_1:= 0;
v_credit_2:= 0;
v_credit_3:= 0;
v_credit_4:= 0;

LOOP
   
     FOR CHILD_REC IN LOAD_CHILD
     //NEED TO ADD CODE SO THAT WHEN THE CURSOR ROW IS N
    //POPULATE
   IF LOAD_CHILD.ROWCOUNt =1 then
         V_EMPID_1 = child_rec.emp_id;
         v_credir_1 = chil_rec.credit;
  elsif LOAD_CHILD.rowcount = 2
      V_EMPID_2 = child_rec.emp_id;
         v_credir_2 = chil_rec.credit;
       ...
end  if;
 

 INSERT INTO TABLE3
 (
  LINE_SEQ          ,
  CREATED_BY        ,
  CODE ,
  EMPYID1           ,
  EMPYID2           ,
  EMPYID3           ,
  EMPYID4           ,
  CREDIT_AMT1       ,
  CREDIT_AMT2       ,
  CREDIT_AMT3       ,
  CREDIT_AMT4    
  )
VALUES
(         db.LINENUMBERSEQUENCE.NEXTVAL,
          LOAD_REC.CREATED_BY ,
          LOAD_REC.CODE ,
          v_empid_1,
          v_empid_2,
          v_empid_3,
          v_empid_4,
         v_credit_1,
         v_credit_2,
         v_credit_3,
         v_credit_4        
          );

 UPDATE TABLE1
  SET PROCESS_FLAG = 'Y'
  where rowid = LOAD_REC.ROWID;
0
 
GGuzdziolCommented:
Either

Drop both cursors and rewrite this is a simple insert-into

-- or --

Tell us your requirements (i.e. the logic used for data flow) and we will try to do that for you.


For now what I see is some kind of parent-child relationship that you try to flatten. This is so called pivot query and the standard way to cope with that would be in your case more or less

INSERT INTO target (id, amt_1, amt_2, amt_3, amt_4)
SELECT p.id,
    MAX(DECODE(c.rn, 1, c.amt, null)),
    MAX(DECODE(c.rn, 2, c.amt, null)),
    MAX(DECODE(c.rn, 3, c.amt, null)),
    MAX(DECODE(c.rn, 4, c.amt, null))
  FROM parent p
    INNER JOIN (SELECT pid, amt, row_number() over (partition by pid order by empid desc) rn FROM child) c ON p.id = c.pid and c.rn < 5
  WHERE p.process_flag = 'N'
  GROUP BY p.id

Are you sure you need 4 first child rows (there's no join condition between parent and child) - this will be 4 same rows multiplied by rows # in parent table...
0
 
countrymeisterAuthor Commented:
Hi ! jinesh_kamdar:

There is one thing i missed in the child cursor I need to get the rows for the parent_id in the first cursor
WHERE THE PARENT ID = FK_ID IN THE CHILD. Can this be done by declaring the cursors beforehand,
Something along these lines.

FOR LOAD_REC in LOAD_DATA
v_empid_1:= '';
v_empid_2:=  '';
v_empid_3:=  '';
v_empid_4:=  '';
v_credit_1:= 0;
v_credit_2:= 0;
v_credit_3:= 0;
v_credit_4:= 0;

LOOP
   CURSOR
        LOAD_CHILD IS
      select *
            from (select *
                       from TABLE2  where LOAD_REC.ERP_ID = TABLE2.FK_ERP_ID
                       order by EMP_ID desc)
            where rownum < 5;
     FOR CHILD_REC IN LOAD_CHILD
      IF LOAD_CHILD.ROWCOUNt =1
         V_EMPID_1 = child_rec.emp_id;
         v_credir_1 = chil_rec.credit;
  elseif LOAD_CHILD.rowcount = 2
      V_EMPID_2 = child_rec.emp_id;
         v_credir_2 = chil_rec.credit;

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Jinesh KamdarCommented:
Yes, you can parametrized cursor.

CURSOR
        LOAD_CHILD (parent_erp_id number) IS
      select *
            from (select *
                       from TABLE2  where FK_ERP_ID = parent_erp_id
                       order by EMP_ID desc)
            where rownum < 5;
0
 
countrymeisterAuthor Commented:
Hi ! GGuzdziol:

That's an amazing query, which I would like to learn.
here is my scenario i have a parent TABLE, child TABLE, and destination table.
the child table could have one or more rows for the parent.
The business requirement is to get all parent rows that have  aproces flag ='N' and only 4 rows from the child table as the destination has only four emp_id and four credit_amt columns.
Also I need to know how many rows were inserted and passed back to the P_ROWS_inserted

Parent table
parent_id,
created_by,
code

Child Table
child_id
emp_id
emp_credit
fk_parent_id

Destination table
dest_id
created_by
code
emp1_id
emp2_id
emp3_id
emp4_id
credit1_amt
credit2_amt
credit3_amt
credit4_amt
0
 
countrymeisterAuthor Commented:
Hi ! GGuzdziol:

Also another question, what would happen if the child table had less than four rows.
The data model supports the optional relationship from parent to child.
There could be a scenario where the parent has no child rows.

Thanks
CM
0
 
countrymeisterAuthor Commented:
Jitesh and GGuzdziol:

thank you both for your help. yesterday I did some reading on pivit tables and was able to solve my problem with the example provided by GGuzdziol.

Jitest also your suggestion works and thanka sgain, but GGuzdziol was pretty cool.
0
 
Jinesh KamdarCommented:
>> GGuzdziol was pretty cool
He always is :)
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now