1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Inserting records in Table using cursor

Discussion in 'SQL PL/SQL' started by rohit_shinez, May 10, 2015.

  1. rohit_shinez

    rohit_shinez Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Hi Guys,

    I need to insert records from one table_1 to master_table by referencing in another ref_table which falls under condition. for e.g..

    Table_1


    col_1 col_2 col_3 col_4
    12 123 200 11-Jun-14
    12 123 3000 12-Jun-14
    12 123 100 13-Jun-14


    Ref_table

    Start date End date Min Max Percentage Type
    10-Jul-2001 15-Jun-2014 0 250 14.90% T1
    10-Jul-2001 15-Jun-2014 250 2500 17% T2
    10-Jul-2001 15-Jun-2014 2500 3500 29.90% T3

    I need to insert into Master_table like this for e.g. for second record in table_1 where col_3 is 3000 that record col_4 falling between start date and end date of Ref_table and split the value like below

    Master_table
    Col_1 col_2 col_4 T1 T2 T3 T1 % T2% T3 %
    12 123 12-Jun-14 250 2250 500 14.90% 17% 29.90%

    I have achieved something like this in cursor but not working

    Code (Text):

    Procedure Procedure(
          ACCOUNT_NO  IN Table_1.ACCOUNT_NUMBER%TYPE,
          SORT_CODE   IN Table_1.SORT_CODE%TYPE,
          MI_ACCT_IDR IN Table_1.MI_ACC_IDENTIFIER%TYPE,
          CALC_SEQ_NO IN Table_1.CALC_SEQ_NO%TYPE )
    IS
     
     
     
     
     
     
    LV_INPUT_REF Ref_table_2.INPUT_REF%TYPE;
    LV_T1_TIER_BALANCE Master_table.T1_TIER_BALANCE%TYPE;
    LV_T2_TIER_BALANCE Master_table.T2_TIER_BALANCE%TYPE;
    LV_T3_TIER_BALANCE Master_table.T3_TIER_BALANCE%TYPE;
    LV_T4_TIER_BALANCE Master_table.T4_TIER_BALANCE%TYPE;
     
     
    CURSOR C1 IS SELECT * FROM Table_1
    WHERE ACCOUNT_NUMBER = ACCOUNT_NO
    AND SORT_CODE = SORT_CODE
    AND MI_ACC_IDENTIFIER = MI_ACCT_IDR
    AND CALC_SEQ_NO = CALC_SEQ_NO;
     
     
    CURSOR C2 IS SELECT MIN_TIER_AMT,MAX_TIER_AMT,NOM_INT_RATE,BM_PRODUCT_CODE
    FROM Ref_Table;
     
     
     
     
    BEGIN
     
     
    dbms_output.put_line('1');
     
     
    SELECT INPUT_REF INTO LV_INPUT_REF FROM Ref_table_2
    where ACCOUNT_NUMBER = ACCOUNT_NO
    AND SORT_CODE = SORT_CODE
    AND MI_ACC_IDENTIFIER = MI_ACCT_IDR
    AND CALC_SEQ_NO = CALC_SEQ_NO and rownum<2;
     
     
    dbms_output.put_line(LV_INPUT_REF);
    FOR I IN C1
    LOOP
    BEGIn
    FOR J IN C2
    LOOP
    IF I.LEDGER_BALANCE between J.MIN_TIER_AMT AND J.MAX_TIER_AMT
    THEN
    LV_T1_TIER_BALANCE := I.LEDGER_BALANCE;
    LV_T2_TIER_BALANCE := 0;
    LV_T3_TIER_BALANCE := 0;
    LV_T4_TIER_BALANCE := 0;
    ELSE
    LV_T1_TIER_BALANCE := 0;
    LV_T2_TIER_BALANCE := I.LEDGER_BALANCE;
    LV_T3_TIER_BALANCE := 0;
    LV_T4_TIER_BALANCE := 0;
    END If;
    INSERT INTO Master_table
    (
    INPUT_REF,
    MI_ACC_IDENTIFIER,
    SORT_CODE,
    ACCOUNT_NUMBER,
    T1_TIER_BALANCE,
    T2_TIER_BALANCE,
    T3_TIER_BALANCE,
    T4_TIER_BALANCE,
    STAGING_LOAD_DATE,
    LED_BAL_DATE,
    REF_AMT,
    CREATED_BY,
    CREATED_ON
    )
    VALUES
    (LV_INPUT_REF,
    I.MI_ACC_IDENTIFIER,
    I.SORT_CODE,
    I.ACCOUNT_NUMBER,
    LV_T1_TIER_BALANCE,
    LV_T2_TIER_BALANCE,
    LV_T3_TIER_BALANCE,
    LV_T4_TIER_BALANCE,
    I.STAGING_LOAD_DATE,
    I.LED_BAL_DATE,
    I.REF_AMT,
    'oracle',
    SYSDATE
    );
    commit;
    END LOOP;
    EXCEPTION
    WHEN OTHERS THEN
    RAISE;
    END;
    END LOOP;
     
     
    END;

     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post create table scripts for the tables in your code, plus insert statements to replicate your data. We can't test your code without them.
     
  3. rohit_shinez

    rohit_shinez Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Please find the scripts

    Code (Text):

    create table master_table(col_1 number,col_2 number,col_4 date,T1 number,t2 number,t3 number,t1_per number,t2_per number,t3_per number);
    create table Ref_table(start_date date,end_date date,min number,max number,percentage number,type varchar2(5));
    create table table_1(col_1 number,col_2 number,col_3 number,col_4 date);


    create ref_table1(col_1 number,col_2 number,start_date date, end_date date;
    insert into ref_table1
    select 12,123,to_date(12-Jun-2014,'DD-MON-YYYY'),to_date(13-Jun-2014,'DD-MON-YYYY') from dual;

    insert into ref_table1
    select 12,123,to_date(13-Jun-2014,'DD-MON-YYYY'),to_date(14-Jun-2014,'DD-MON-YYYY') from dual;


     
    i Have achieved the output now i need to transform the data into monthly_table by referring ref_table1 where col_4 is between start_date and end_date of ref_table1 and sum the values and count the number of respective types where not equal to zero.

    COL_1 COL_2 COL_4 T1 T2 T3 T1PER T2PER T3PER
    ---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
    12 123 11-Jun-2014 200 0 0 14.9 17 29.9
    12 123 12-Jun-2014 250 2500 250 14.9 17 29.9
    12 123 13-Jun-2014 100 0 0 14.9 17 29.9

    Ref_table1
    col_1,col_2,start_date, end_date
    12,123,12-Jun-2014 , 13-Jun-2014
    12,123,13-Jun-2014, 14-Jun-2014


    Monthly_table(output)

    col_1,col_2,start_date,end_date,T1,T2,T3,T1_days,T2_days,T3_days
    12,123,12-jun-2014,13-jun-2014,350,2500,250,2,1,1


    I tried like below

    Code (Text):

    SELECT a.col_1,
           a.col_2,
           b.start_date,
           b.end_date,
           Sum(a.t1),
           Sum(a.t2),
           Sum(a.t3),
           Count(*)
    FROM   master_table a,
           ref_table1 b
    WHERE  a.col_4_date BETWEEN b.start_date AND b.end_date
           AND a.col_1 = b.col_1
           AND a.col_2 = b.col_2
     
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    There are several problems with your response:


    Your create statement for ref_table1 is incorrect; it's missing the TABLE keyword and a closing parenthesis
    Your insert statements do not present the date strings as strings; they are missing enclosing ''
    You have no data in master_table
    You have no group by in your query
    There is no column named col_4_date in any table in this example


    I have 'fixed' your example to actually run, have data and return results:


    Code (SQL):

    SQL> CREATE TABLE master_table(col_1 NUMBER,col_2 NUMBER,col_4 DATE,T1 NUMBER,t2 NUMBER,t3 NUMBER,t1_per NUMBER,t2_per NUMBER,t3_per NUMBER);


    TABLE created.


    SQL> CREATE TABLE Ref_table(start_date DATE,end_date DATE,MIN NUMBER,MAX NUMBER,percentage NUMBER,TYPE varchar2(5));


    TABLE created.


    SQL> CREATE TABLE table_1(col_1 NUMBER,col_2 NUMBER,col_3 NUMBER,col_4 DATE);


    TABLE created.


    SQL> CREATE TABLE ref_table1(col_1 NUMBER,col_2 NUMBER,start_date DATE, end_date DATE);


    TABLE created.


    SQL>
    SQL> INSERT INTO ref_table1
      2  SELECT 12,123,to_date('12-Jun-2014','DD-MON-YYYY'),to_date('13-Jun-2014','DD-MON-YYYY') FROM dual;


    1 ROW created.


    SQL>
    SQL> INSERT INTO ref_table1
      2  SELECT 12,123,to_date('13-Jun-2014','DD-MON-YYYY'),to_date('14-Jun-2014','DD-MON-YYYY') FROM dual;


    1 ROW created.


    SQL>
    SQL> BEGIN
      2          FOR i IN 1..100 loop
      3                  INSERT INTO master_table
      4                  VALUES(i, i, to_date('12-Jun-2014','DD-MON-YYYY') + i, i, i, i, i, i, i);
      5          END loop;
      6          INSERT INTO master_table
      7          VALUES(12, 123, to_date('13-Jun-2014','DD-MON-YYYY'), 1, 1, 1, 1, 1, 1);
      8  END;
      9  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> SELECT a.col_1,
      2         a.col_2,
      3         b.start_date,
      4         b.end_date,
      5         SUM(a.t1),
      6         SUM(a.t2),
      7         SUM(a.t3),
      8         COUNT(*)
      9  FROM   master_table a,
     10         ref_table1 b
     11  WHERE  a.col_4 BETWEEN b.start_date AND b.end_date
     12         AND a.col_1 = b.col_1
     13         AND a.col_2 = b.col_2
     14  GROUP BY a.col_1, a.col_2, b.start_date, b.end_date
     15  /


         COL_1      COL_2 START_DAT END_DATE   SUM(A.T1)  SUM(A.T2)  SUM(A.T3)   COUNT(*)
    ---------- ---------- --------- --------- ---------- ---------- ---------- ----------
            12        123 12-JUN-14 13-JUN-14          1          1          1          1
            12        123 13-JUN-14 14-JUN-14          1          1          1          1


    2 ROWS selected.


    SQL>
     

    Please notice the difference between the working code and yours.