Forum Stats

  • 3,816,029 Users
  • 2,259,131 Discussions
  • 7,893,366 Comments

Discussions

How to merge two INDEX BY tables?

CGMS
CGMS Member Posts: 68
edited Feb 23, 2010 4:38AM in SQL & PL/SQL
Hi All,

Is there any way to merge the two INDEX BY tables?

i tried MULTISET UNION DISTINCT from the below link:
http://www.oracle.com/technology/oramag/oracle/03-sep/o53plsql.html

I got the PLS:00306: wrong number or types of arguments in call to 'MULTISET_UNION_DISTINCT' error.

Code:

declare

TYPE rec_nbrchk IS TABLE OF test.rec_nbr%TYPE
INDEX BY BINARY_INTEGER;

rec_nbr_chk rec_nbrchk;
mlcl_scope_chk rec_nbrchk;
all_chk rec_nbrchk;

begin

all_chk := (rec_nbr_chk )
MULTISET UNION DISTINCT
(scope_chk);
end;

Thanks in advance.

Suresh.
Tagged:

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited Feb 23, 2010 1:46AM
    You cant use MULTISET with INDEX BY tables. You must do it with PL/SQL Nested Table Type.
    declare
     TYPE rec_nbrchk IS TABLE OF emp.empno%TYPE;
    
     rec_nbr_chk rec_nbrchk;
     mlcl_scope_chk rec_nbrchk; 
     all_chk rec_nbrchk;
    begin
     all_chk := (rec_nbr_chk ) MULTISET UNION DISTINCT (mlcl_scope_chk);
    end;
    This mentioned in the first line of the document. Please read the content of the below link.

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/operators006.htm
  • Twinkle
    Twinkle Member Posts: 740 Silver Badge
    edited Feb 23, 2010 1:54AM
    Hi,

    Check the below link:

    Under the header :Type Demos

    http://www.psoug.org/reference/type.html

    Something like this??
    SQL> Declare
      2  TYPE type_rec_emp is table of emp.empno%type;
      3  rec_empno1 type_rec_emp;
      4  rec_empno2 type_rec_emp;
      5  rec_empno3 type_rec_emp;
      6  Begin
      7  rec_empno3 := 
      8   rec_empno1
      9   MULTISET UNION DISTINCT
     10   rec_empno2;
     11  End;
     12  /
    
    PL/SQL procedure successfully completed.
    Twinkle
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    edited Feb 23, 2010 2:38AM
    Please do not refer people to the old unsupprted psoug site.

    Morgan's Library has been relocated to www.morganslibrary.org with the main library page as www.morganslibrary.org/library.html.

    The specific page you refer to is:
    http://www.morganslibrary.org/reference/type.html
    and has been updated several times since the ancient one was written.

    Thank you for updating all of your links to the new site too.

    Twinkle: Please use the edit button to update your link to the correct one. Thank you.
  • CGMS
    CGMS Member Posts: 68
    Thanks for all of your answers.

    But i need to merge the two INDEX BY tables.

    is there any any way to do merge between two INDEX BY tables?


    Suresh.
  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    Loop through one and append it to the other is about your only option (that i'm aware of).
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    declare
       type rec_nbrchk is table of emp.empno%type
                             index by binary_integer;
    
       rec_nbr_chk      rec_nbrchk;
       mlcl_scope_chk   rec_nbrchk;
       all_chk          rec_nbrchk;
    begin
       for i in 1 .. rec_nbr_chk.count
       loop
          all_chk (i) := rec_nbr_chk (i);
       end loop;
    
       for i in 1 .. mlcl_scope_chk.count
       loop
          all_chk (i + rec_nbr_chk.count) := mlcl_scope_chk (i);
       end loop;
    end;
    MichaelS
  • CGMS
    CGMS Member Posts: 68
    Hi,


    The last post is very useful for me.

    I want distinct records from these two INDEX BY tables.
    Please let me know if there are any way to distinct all_chk INDEX BY table.

    Suresh.
  • CGMS
    CGMS Member Posts: 68
    Hi all,

    i came to conclusion to find the DISTINCT records by following approach:

    declare
    type rec_nbrchk is table of emp.empno%type
    index by binary_integer;

    rec_nbr_chk rec_nbrchk;
    mlcl_scope_chk rec_nbrchk;
    all_chk rec_nbrchk;
    chk_flg BOOLEAN;
    idx NUMBER;
    begin
    for i in 1 .. rec_nbr_chk.count
    loop
    all_chk (i) := rec_nbr_chk (i);
    end loop;

    for i in 1 .. mlcl_scope_chk.count
    loop
    chk_flg := true;
    idx:= 1;
    while idx <= all_chk.count
    loop
    if all_chk(idx) = mlcl_scope_chk(i) then
    chk_flg := false;
    exit;
    end if;
    end loop;
    if chk_flg = true then
    all_chk (i + rec_nbr_chk.count) := mlcl_scope_chk (i);
    end if;
    end loop;
    end;


    is there any other ways to retrieve distinct records from joining of two INDEX BY tables?

    Suresh.
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    But i need to merge the two INDEX BY tables.
    But you could probably use an intermediate associate array?
    SQL> select empno
      from emp
     where empno > 7500
    /
         EMPNO
    ----------
          7521
          7566
          7654
          7698
          7782
          7788
          7839
          7844
          7876
          7900
          7902
          7934
    
    12 rows selected.
    
    SQL> select empno
      from emp
     where empno < 7700
    /
         EMPNO
    ----------
          7369
          7499
          7521
          7566
          7654
          7698
    
    6 rows selected.
    
    SQL> declare
       type rec_nbrchk is table of emp.empno%type index by binary_integer;
    
       type all_chk_tab_tmp is table of emp.empno%type;
    
       rec_nbr_chk      rec_nbrchk;
       mlcl_scope_chk   rec_nbrchk;
       all_chk          rec_nbrchk;
       all_chk_tmp      all_chk_tab_tmp := all_chk_tab_tmp ();
    begin
     /*  Just for demonstration purpose: Fill the arrays with some values */
       select empno
         bulk collect into rec_nbr_chk
         from emp
        where empno > 7500;
    
       select empno
         bulk collect into mlcl_scope_chk
         from emp
        where empno < 7700;
    
       for i in 1 .. rec_nbr_chk.count + mlcl_scope_chk.count
       loop
          all_chk_tmp.extend;
          all_chk_tmp (i) :=
             case
                when i <= rec_nbr_chk.count  then rec_nbr_chk (i)
                else mlcl_scope_chk (i - rec_nbr_chk.count )
             end;
       end loop;
    
       all_chk_tmp := all_chk_tmp multiset union distinct all_chk_tmp;
    
       for i in 1 .. all_chk_tmp.count
       loop
          all_chk (i) := all_chk_tmp (i);
          dbms_output.put_line( to_char(i,'fm00') || ': ' || all_chk (i));
       end loop;
    end;
    /
    01: 7521
    02: 7566
    03: 7654
    04: 7698
    05: 7782
    06: 7788
    07: 7839
    08: 7844
    09: 7876
    10: 7900
    11: 7902
    12: 7934
    13: 7369
    14: 7499
    PL/SQL procedure successfully completed.
    MichaelS
This discussion has been closed.