Home » SQL & PL/SQL » SQL & PL/SQL » Elimiate duplicate name/values from VARRAY from pl/sql oracle 9i (How to elimiate duplicate name/values from VARRAY from pl/sql oracle 9i)
Elimiate duplicate name/values from VARRAY from pl/sql oracle 9i [message #443372] Mon, 15 February 2010 06:16 Go to next message
sitesh.mukherjee
Messages: 5
Registered: December 2009
Location: India
Junior Member
Guys,

I've a table like below data.

Table_1:
Name Object_Name
----------------------
1001 CUST_DATA
1001 ITEM_DATA
1001 SALES_DATA
1002 CUST_DATA
1002 ITEM_DATA
1002 RATE_DATA
1002 GROUP_DATA

Now from a procedure using Varray, I'm populating the "Object_Name" from the Table_1 compare with "Name".

While I'm creating Varray through pl/sql block for Object_name's, I'm getting the data from the Varray like below.

CUST_DATA
ITEM_DATA
SALES_DATA
CUST_DATA
ITEM_DATA
RATE_DATA
GROUP_DATA

Now my actual requirement is to eliminate the duplicate names from Varray and generate the Varray list with distinct object names , like below.

CUST_DATA
ITEM_DATA
SALES_DATA
RATE_DATA
GROUP_DATA

Can anybody help me on this?

Thanks is advance.....
Re: Elimiate duplicate name/values from VARRAY from pl/sql oracle 9i [message #443373 is a reply to message #443372] Mon, 15 February 2010 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Populate using DISTINCT.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Regards
Michel

[Updated on: Mon, 15 February 2010 06:18]

Report message to a moderator

Re: Elimiate duplicate name/values from VARRAY from pl/sql oracle 9i [message #443377 is a reply to message #443372] Mon, 15 February 2010 06:32 Go to previous messageGo to next message
sitesh.mukherjee
Messages: 5
Registered: December 2009
Location: India
Junior Member
Michael,

If I use the DISTINCT clause from query comparing Name & Object_Name, then also I'll get duplicate values for Object Names. So, distinct clause can't be used.

My requirement is to create a Single dimention VARRAY for Object names, where only distinct object_names should be considered. And based on the result I need to write other business logic as per requirement.

My present code is like below:
-------------------------------
CREATE OR REPLACE procedure varray_test1
is
type app_desc is varray(50) of Table_1.Object_Name%type;
a_desc app_desc := app_desc();
indx number := 1;
begin
for r1 in
(select object_name from Table_1
where name in('1001','1002')) loop
if indx < a_desc.limit then
a_desc.extend;
a_desc(a_desc.last) := r1.referenced_name;
dbms_output.put_line(a_desc(indx));
indx := indx +1;
end if;
end loop;
end;


I want to eliminate the duplicate names befor useing EXTEND.

If somebody gives me the solution using Nested Table, that is also be ok with me.

Thanks in advance...

Re: Elimiate duplicate name/values from VARRAY from pl/sql oracle 9i [message #443381 is a reply to message #443377] Mon, 15 February 2010 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
select DISTINCT object_name from Table_1
where name in('1001','1002'))

Regards
Michel
Re: Elimiate duplicate name/values from VARRAY from pl/sql oracle 9i [message #443389 is a reply to message #443377] Mon, 15 February 2010 08:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Add a DISTNCT:
CREATE OR REPLACE procedure varray_test1
is
type app_desc is varray(50) of Table_1.Object_Name%type;
a_desc app_desc := app_desc();
indx number := 1;
begin
for r1 in 
(select distinct object_name from Table_1
where name in('1001','1002')) loop
if indx < a_desc.limit then
a_desc.extend;
a_desc(a_desc.last) := r1.referenced_name;
dbms_output.put_line(a_desc(indx));
indx := indx +1;
end if; 
end loop;
end;

[too slow!!!!!]

[Updated on: Mon, 15 February 2010 08:04]

Report message to a moderator

Re: Elimiate duplicate name/values from VARRAY from pl/sql oracle 9i [message #443505 is a reply to message #443389] Tue, 16 February 2010 01:47 Go to previous messageGo to next message
sitesh.mukherjee
Messages: 5
Registered: December 2009
Location: India
Junior Member
Guys,

I've given the above query as an example for better understanding.

But, the actual query contain some analytical functions also (like COUNT(*) over partition, ROW_NUMBER over partition etc.) to get the count and sequence.So I can't use the DISTINCT clause here.

I need to eliminate the duplicate Object_Names from the VARRAY result, which is populating through cursor.

Actual scenario data like below:
--------------------------------
Name Object_Name CNT SEQ
1001 CUST_DATA 3 1
1001 ITEM_DATA 3 2
1001 SALES_DATA 3 3
1002 CUST_DATA 4 1
1002 ITEM_DATA 4 2
1002 RATE_DATA 4 3
1002 GROUP_DATA 4 4

Present VARRAY data like below:
-------------------------------
CUST_DATA
ITEM_DATA
SALES_DATA
CUST_DATA
ITEM_DATA
RATE_DATA
GROUP_DATA

I want the data like below into the VARRAY:
-------------------------------------------
CUST_DATA
ITEM_DATA
SALES_DATA
RATE_DATA
GROUP_DATA

Can anybody help this? Solution from nested table option also will be ok with me.

Thanks in advance...
Re: Elimiate duplicate name/values from VARRAY from pl/sql oracle 9i [message #443509 is a reply to message #443505] Tue, 16 February 2010 01:51 Go to previous message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Show me a query where you cannot add a DISTINCT maybe adding a new level.

Regards
Michel
Previous Topic: oracle to moss
Next Topic: Sequence generation help
Goto Forum:
  


Current Time: Sat Nov 09 18:21:25 CST 2024