This forum is currently locked. You can't register or post questions at this time. (read more)

populate unique values in a collection

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

yairk30
Member
Posts: 35
Joined: Thu Apr 20, 2006 6:05 am

populate unique values in a collection

Postby yairk30 » Mon Jun 25, 2012 7:56 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: populate unique values in a collection

Postby Tim... » Tue Jun 26, 2012 6:52 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

yairk30
Member
Posts: 35
Joined: Thu Apr 20, 2006 6:05 am

Re: populate unique values in a collection

Postby yairk30 » Tue Jun 26, 2012 4:02 pm

100%

thanks!

yair

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: populate unique values in a collection

Postby Tim... » Tue Jun 26, 2012 6:50 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 5 guests

cron