How to select distinct values from collection?
463808Apr 22 2008 — edited Apr 23 2008How do I act on each distinct value in an Associative Array?
Sub-questions are: how do I reference the array column (distinct(*) is not correct)? and how do I reference values in the loop (y.value?)?
DECLARE
TYPE siteArray IS TABLE OF VARCHAR2(30)
INDEX BY BINARY_INTEGER;
newSites siteArray;
BEGIN
/* populate array */
newSites(1) := 'A';
newSites(2) := 'A';
newSites(3) := 'B';
newSites(4) := 'A';
newSites(5) := 'B';
newSites(6) := 'C';
newSites(7) := 'C';
newSites(8) := 'C';
newSites(9) := 'A';
/* find distinct values */
FOR y IN ( SELECT DISTINCT(*) FROM TABLE ( CAST ( newsites AS siteArray ) ) )
LOOP
...do something on 'A'...
...do something on 'B'...
...do something on 'C'...
END LOOP;
END;