Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Merge statement with Nested tables

    I have a procedure as shown below which works fine. Now the cursor definition is changed to read from two databases.
    procedure p_load
    is

    cursor c1 is
    select a,sum(b),count(c),d
    from tab1

    TYPE v_tab IS TABLE OF c1%ROWTYPE

    begin

    open c1
    loop
    fetch c1 bulk collect into v_tab
    limit 100000;

    v_count := v_count + v_tab.count;

    begin
    FORALL i in 1..v_tab.count
    INSERT into final_tab
    values v_tab(i);

    ins_cnt := ins_cnt + v_tab.count;
    end;
    exit when c1%notfound;
    end loop;
    close c1;

    commit;

    end p_load;
    But now I have to select from 2 tables and there may be records with same cc_acct_id but different values for b,c and d. what I want to do is sum it up grouping by cc_acct_id.

    cursor c1 is
    select a,sum(b),count(c),d from tab1
    union all
    select a,sum(b),count(c),d from tab2;

    a -> cc_acct_id
    b -> call_time
    c -> c_cnt
    d -> call_dt

    cc_acct_id call_time c_cnt call_dt
    -----------------------------------------------------
    100 200 1 9/13/09 **
    200 34 1 9/1/09
    100 40 2 9/13/09 **
    100 23 3 9/29/09


    The accounts marked with a star have to be grouped by cc_acct_id.

    So, I'll end up with ---
    cc_acct_id call_time c_cnt call_dt
    -----------------------------------------------------
    100 240 2 9/13/09 **
    100 23 3 9/29/09
    200 34 1 9/1/09

    I want to use a Merge while inserting into table FINAL_TAB. Update when it finds a record with the same cc_acct_id or insert if its a new record. Any ideas on how I can acheive this.
    Can someone please guide me through this.

    Thanks in advance!
    Nandini
    Last edited by nandinir; 11-30-09 at 12:37.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    When all else fails, Read The Fine Manual

    MERGE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    begin
    FORALL i in 1..v_tab.count
    INSERT into final_tab
    values v_tab(i);
    ins_cnt := ins_cnt + v_tab.count;
    end;
    exit when c1%notfound;
    end loop;
    close c1;
    commit;
    end p_load;

    I replaced the code in Bold above to this, but its doesnt work.
    When i compile this procedure in TOAD, it says- 'Compiled with compilation errors' and when I check the errors, it says 'No errors'.

    MERGE INTO final_tab D
    USING (SELECT * FROM TABLE(v_tab(i))) S
    ON (D.cc_acct_id = S.cc_acct_id)
    WHEN MATCHED THEN UPDATE
    SET D.c_cnt = s. c_cnt + d.c_cnt,
    D.call_time = S.call_time + D.call_time

    WHEN NOT MATCHED THEN
    INSERT (call_dt,cc_acct_id,c_cnt,
    call_time)
    VALUES (s.call_dt,s.cc_acct_id,s.c_cnt,
    s.call_time);

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >When i compile this procedure in TOAD,
    use sqlplus instead

    Then use CUT & PASTE so we can see whole session; your input & Oracle's response.


    Post Operating System (OS) name & version for DB server system.
    Post results of
    SELECT * from v$version
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Don't they recommend SQL Developer these days?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Don't they recommend SQL Developer these days?
    Whoever "they" may be, "they" is not me.
    sqlplus allows for CUT & PASTE & is ideal for posting to forums!
    sqlplus does not have to be used all the time, but it has it uses, IMO!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •