-
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?
-
Why don't you post script?
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|