populate unique values in a collection

[yairk30]

Hey Tim and how are you!
I need to populate a collection with values from a table in order that every brand of value from the table will be represented 1 time only in the collection.
For example , let say we have a table "agreement" with one column "agreement_num" (varchar2). This column contain a 200 records with max 3 brand of values ("agreem num 1" , "agreem num 2" etc.).
Now , when I populate the collection I want it to contain 1 item for every different value from the table.
Agreement_arr(1) = "agreem num 1" ,
Agreement_arr(2) = "agreem num 2"
Etc.
Note: the logic in order to obtain the data from the table is difficult.
I canot just run with a cursor like

Code: Select all

select distinct  agreement_num  from agreement

.
I need a way for looking up inside the array in order to check if the new value fetching from the table is already represented in the array or not.
(running a loop on the array to compare with the value fetched from the table every time , is not a good idea of course.)
I need something similar to exists method (but for checking existence of a string values in the array , not just a n items) .
Hope I made myself clear.

I'm on 8i version.

Thanks,
yair

[Tim...]

Hi.

OK. You can't figure out a way to pull the right rows directly. In that case, use your complex logic to populate a global temporary table.

http://www.oracle-base.com/articles/8i/ ... tables.php

From there you can do a select distinct into your collection. I think this will probably be a faster that putting the data directly into the collection, then trying to filter it.

Cheers

Tim...

[yairk30]

100%

thanks!

yair

[Tim...]