Unique rows in a nested table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Unique rows in a nested table

  1. #1
    Join Date
    Mar 2007
    Posts
    2

    Unique rows in a nested table

    Hello everyone, I just started scripting in PL/SQL and I could use a bit of help with something.

    I use Oracle 9, SQL*PLUS 9 and TOAD 8.

    My script generate a huge list of values in a nested table. A lot of those values are identical (copies). I would like to keep only the unique in my nested table. Doing the equivalent of a SELECT DISTINCT on my nested table, preferrably outputting the result in a nested table (or at worst, something else).

    I've tried a few things, and looked the doc, I couldn't find a non-ugly way to do it. Someone have an idea?

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Why don't you post script?

  3. #3
    Join Date
    Sep 2005
    Posts
    278
    If you were using Oracle10g then you could use the new set operations.

    Code:
    DECLARE
    	TYPE nums IS TABLE OF NUMBER;
      	num1 nums := nums(1,2,2,3,3,4);
    	num2 nums;
    BEGIN
    	num2 := num1 MULTISET UNION DISTINCT num1;
    FOR i IN num2.first..num2.last
    LOOP
    	dbms_output.put_line(num2(i));
    END LOOP;
    END;
    SQL> /
    1
    2
    3
    4
    
    PL/SQL procedure successfully completed.
    Last edited by tabreaz; 03-15-2007 at 01:26 AM.

  4. #4
    Join Date
    Mar 2007
    Posts
    2

    Well

    Well I didn't feel it was neccessary to post the script, it wouldn't tell you much. It just generate a list of values in a nested table, and I need to get rid of the duplicates entry in this nested table.

    I did some research yesterday. Apparently there is no way to do it simply outside of Oracle 10. So I don't know, I guess I'll take a look at what the other collection type offer me. Due to the huge amount of data, I can't afford an ugly solution that will run slowly.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Code:
    SQL> get nested_table_2
      1  declare
      2  TYPE NumList IS TABLE OF INTEGER;
      3  nums NumList := Numlist(12,10,8,8,10,12,7,3,7,7);
      4  L_temp int;
      5  begin
      6    dbms_output.put_line('before  sorting ');
      7    FOR I in nums.FIRST ..nums.LAST Loop
      8       dbms_output.put_line(nums(I));
      9    END loop;
     10    --- sort netsted table
     11    FOR M in nums.FIRST .. (nums.LAST - 1) LOOP
     12        FOR N in (M+1) .. nums.LAST LOOP
     13            if nums(M) > nums(N) then
     14               -- swap the values
     15               L_temp := nums(N);
     16               nums(N) := nums(M);
     17               nums(M) := L_temp;
     18            end if;
     19        END LOOP;
     20    END LOOP;
     21    --- using first and last
     22    dbms_output.put_line('after sorting ');
     23    FOR I in nums.FIRST ..nums.LAST Loop
     24       dbms_output.put_line(nums(I));
     25    END loop;
     26    --- delete duplicate
     27    FOR M in nums.FIRST .. (nums.LAST -1) Loop
     28        if nums(M) >= nums(M+1) then
     29           nums(M) := NULL ;
     30        end if;
     31    END loop;
     32    dbms_output.put_line('after deleting duplicates ');
     33    FOR I in nums.FIRST ..nums.LAST Loop
     34       dbms_output.put_line(nums(I));
     35    END loop;
     36* end;
     37  /
    before  sorting
    12
    10
    8
    8
    10
    12
    7
    3
    7
    7
    after sorting
    3
    7
    7
    7
    8
    8
    10
    10
    12
    12
    after deleting duplicates
    3
    7
    8
    10
    12
    
    PL/SQL procedure successfully completed.
    Hope this helps...

Posting Permissions

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



Click Here to Expand Forum to Full Width