Home » SQL & PL/SQL » SQL & PL/SQL » Finding distinct elements from Array (Oracle 9i)
Finding distinct elements from Array [message #331987] Mon, 07 July 2008 02:43 Go to next message
deepayan
Messages: 51
Registered: December 2005
Member
Hi All,

I have a PL/SQL table which is of a record type.
  TYPE TEST IS RECORD (ROW1 NUMBER,
                       ROW2 NUMBER);
 TYPE TEST_ARR IS TABLE OF TEST  INDEX BY BINARY_INTEGER ;                 
  V_TEST_ARR TEST_ARR; 


The data is like this:
  
 1  2
 1  3
 2  4
 5  2
 3  1
 3  6
 4  2
 6  3
 2  5 

Now , I need all the distinct records from the PL/SQL table.
That is , 1 2 and 2 1 are treated as same records. So we need only the 1 2 & discard 2 1.
Also the output should be sorted by ROW1 of the record.
So the output will be :
  1  2
  1  3
  2  4
  2  5
  3  6
 


Please help..!!

Thanks in Advance ,
Deepayan
Re: Finding distinct elements from Array [message #331989 is a reply to message #331987] Mon, 07 July 2008 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think DISTINCT, LEAST and GREATEST will do the trick.

Of course if you'd posted a test case as requested by the guidelines, maybe you'd have a more precise answer.

Regards
Michel
Re: Finding distinct elements from Array [message #332012 is a reply to message #331987] Mon, 07 July 2008 04:29 Go to previous messageGo to next message
deepayan
Messages: 51
Registered: December 2005
Member
Hi ,
Do you have any example for similar kind of code? If yes , then please provide the link.

Thanks,
Deepayan
Re: Finding distinct elements from Array [message #332031 is a reply to message #332012] Mon, 07 July 2008 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL Reference, all the mentioned functions are there.

Regards
Michel
Re: Finding distinct elements from Array [message #332051 is a reply to message #331987] Mon, 07 July 2008 06:37 Go to previous messageGo to next message
deepayan
Messages: 51
Registered: December 2005
Member
Thanks.
Don't you have a ready program for this logic?

Thanks,
Deepayan
Re: Finding distinct elements from Array [message #332053 is a reply to message #332051] Mon, 07 July 2008 06:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, but it takes a couple of minutes to write it, just try it.

Regards
Michel
Re: Finding distinct elements from Array [message #332065 is a reply to message #332053] Mon, 07 July 2008 07:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This would have been a lot easier if you'd bothered to post a test case, but here's a way of doing it:
declare

  TYPE distinct_test IS record (col_1 number, col_2 number);
  TYPE distinct_TEST_ARR IS TABLE OF distinct_test index by binary_integer;
  TYPE neat_trick IS TABLE OF distinct_test index by varchar2(30);

  t_test_arr distinct_test_arr;
  
  t_results  neat_trick;
  
 -- Function to order entries in record so that lowest is always first
  function make_key (r_in  distinct_test) return varchar2 is
   v_Return varchar2(30) ;
  begin
    if r_in.col_1 > r_in.col_2 then
      v_return := to_char(r_in.col_2,'fm0000')||to_char(r_in.col_1,'fm0000');
    else
      v_return := to_char(r_in.col_1,'fm0000')||to_char(r_in.col_2,'fm0000');
    end if;
    return v_return;
     
 end make_key;

  function make_row (v_1 in number, v_2 in number) return distinct_test is
    r_return  distinct_test;
  begin
    r_return.col_1 := v_1;
    r_return.col_2 := v_2;
    
    return r_return;
  end make_row;
 
begin
-- Build test data
 t_test_arr(1) := make_row(1,  2);
 t_test_arr(2) := make_row(1,  3);
 t_test_arr(3) := make_row(2,  4);
 t_test_arr(4) := make_row(5,  2);
 t_test_arr(5) := make_row(3,  1);
 t_test_arr(6) := make_row(3,  6);
 t_test_arr(7) := make_row(4,  2);
 t_test_arr(8) := make_row(6,  3);
 t_test_arr(9) := make_row(2,  5);

  for i in t_test_arr.first .. t_test_arr.last loop
    if t_test_arr.exists(i) then
      t_results(make_key(t_test_arr(i))) := t_test_arr(i);
    end if;
  end loop;

  dbms_output.put_line('Results: '||t_results.count);
  idx := t_results.first;
  loop
    dbms_output.put_line('Key '||idx||' Value '||t_results(idx).col_1||' '||t_results(idx).col_2);
    if idx = t_results.last then
      exit;
    end if;
    idx := t_results.next(idx);
  end loop;
end;
/


If you change your arrays from Pl/Sql ones to Sql ones, you could use the SET or MULTISET operators.
Re: Finding distinct elements from Array [message #332168 is a reply to message #332065] Mon, 07 July 2008 14:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
I think I would be inclined to get it into a sql type one way or another, so that I could use ordinary SQL, as shown below.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE test_rec AS OBJECT
  2    (row1  NUMBER,
  3  	row2  NUMBER);
  4  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE test_tab AS TABLE OF test_rec;
  2  /

Type created.

SCOTT@orcl_11g> DECLARE
  2    TYPE test IS RECORD (row1 NUMBER,
  3  			    row2 NUMBER);
  4    TYPE test_arr IS TABLE OF test INDEX BY BINARY_INTEGER ;
  5    v_test_arr test_arr;
  6    v_test_tab test_tab := test_tab();
  7  BEGIN
  8    v_test_arr(1).row1 := 1;
  9    v_test_arr(1).row2 := 2;
 10    v_test_arr(2).row1 := 1;
 11    v_test_arr(2).row2 := 3;
 12    v_test_arr(3).row1 := 2;
 13    v_test_arr(3).row2 := 4;
 14    v_test_arr(4).row1 := 5;
 15    v_test_arr(4).row2 := 2;
 16    v_test_arr(5).row1 := 3;
 17    v_test_arr(5).row2 := 1;
 18    v_test_arr(6).row1 := 3;
 19    v_test_arr(6).row2 := 6;
 20    v_test_arr(7).row1 := 4;
 21    v_test_arr(7).row2 := 2;
 22    v_test_arr(8).row1 := 6;
 23    v_test_arr(8).row2 := 3;
 24    v_test_arr(9).row1 := 2;
 25    v_test_arr(9).row2 := 5;
 26    v_test_tab.EXTEND (v_test_arr.COUNT);
 27    FOR i IN 1 .. v_test_arr.COUNT LOOP
 28  	 v_test_tab(i) := test_rec (v_test_arr(i).row1, v_test_arr(i).row2);
 29    END LOOP;
 30    FOR r IN
 31  	 (SELECT DISTINCT t1.*
 32  	  FROM	 TABLE (CAST (v_test_tab AS test_tab)) t1
 33  	  WHERE  t1.row1 <= t1.row2
 34  	  OR	 (t1.row1, t1.row2) NOT IN
 35  		 (SELECT t2.row2, t2.row1
 36  		  FROM	 TABLE (CAST (v_test_tab AS test_tab)) t2)
 37  	  ORDER  BY row1, row2)
 38    LOOP
 39  	 DBMS_OUTPUT.PUT_LINE (r.row1 || '  ' || r.row2);
 40    END LOOP;
 41  END;
 42  /
1  2
1  3
2  4
2  5
3  6

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

[Updated on: Mon, 07 July 2008 14:20]

Report message to a moderator

Re: Finding distinct elements from Array [message #332174 is a reply to message #332168] Mon, 07 July 2008 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> DECLARE
  2    TYPE test IS RECORD (row1 NUMBER, row2 NUMBER);
  3    TYPE test_arr IS TABLE OF test INDEX BY BINARY_INTEGER ;
  4    v_test_arr test_arr;
  5    v_test_tab test_tab := test_tab();
  6  BEGIN
  7    v_test_arr(1).row1 := 1;
  8    v_test_arr(1).row2 := 2;
  9    v_test_arr(2).row1 := 1;
 10    v_test_arr(2).row2 := 3;
 11    v_test_arr(3).row1 := 2;
 12    v_test_arr(3).row2 := 4;
 13    v_test_arr(4).row1 := 5;
 14    v_test_arr(4).row2 := 2;
 15    v_test_arr(5).row1 := 3;
 16    v_test_arr(5).row2 := 1;
 17    v_test_arr(6).row1 := 3;
 18    v_test_arr(6).row2 := 6;
 19    v_test_arr(7).row1 := 4;
 20    v_test_arr(7).row2 := 2;
 21    v_test_arr(8).row1 := 6;
 22    v_test_arr(8).row2 := 3;
 23    v_test_arr(9).row1 := 2;
 24    v_test_arr(9).row2 := 5;
 25    --
 26    v_test_tab.EXTEND (v_test_arr.COUNT);
 27    FOR i IN 1 .. v_test_arr.COUNT LOOP
 28      v_test_tab(i) := test_rec (v_test_arr(i).row1, v_test_arr(i).row2);
 29    END LOOP;
 30    --
 31    FOR r IN (
 32      SELECT DISTINCT LEAST(t.row1,t.row2) l, GREATEST(t.row1,t.row2) g
 33      FROM TABLE (CAST (v_test_tab AS test_tab))  t
 34      ORDER BY l, g
 35    ) LOOP
 36      DBMS_OUTPUT.PUT_LINE(r.l||' '||r.g);
 37    END LOOP;
 38    --
 39  END;
 40  /
1 2
1 3
2 4
2 5
3 6

PL/SQL procedure successfully completed.

Regards
Michel

[Updated on: Mon, 07 July 2008 14:30]

Report message to a moderator

Re: Finding distinct elements from Array [message #332192 is a reply to message #332174] Mon, 07 July 2008 16:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Mon, 07 July 2008 12:29

SELECT DISTINCT LEAST(t.row1,t.row2) l, GREATEST(t.row1,t.row2) g
FROM TABLE (CAST (v_test_tab AS test_tab)) t
ORDER BY l, g



Yes, that's much simpler. I can't believe I missed that. I must need more caffeine or sleep or both or maybe sleep then caffeine.
Re: Finding distinct elements from Array [message #332238 is a reply to message #331987] Tue, 08 July 2008 00:18 Go to previous messageGo to next message
deepayan
Messages: 51
Registered: December 2005
Member
Thanks Michel , Barbara , JRowbottom for your thoughtfull replies. Now , I got an insight to the logic.

Just one clarification , in spite of using a SQL type , can I use the same pl/sql array and loop it through twice to achieve the same answer.

Thanks,
Deepayan
Re: Finding distinct elements from Array [message #332305 is a reply to message #332192] Tue, 08 July 2008 02:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
DECLARE
TYPE test IS RECORD (row1 NUMBER,
		    row2 NUMBER);
TYPE test_arr IS TABLE OF test INDEX BY BINARY_INTEGER ;
   v_test_arr    test_arr;
   v_test_tab    test_tab := test_tab();

 BEGIN
   v_test_arr(1).row1 := 1;
   v_test_arr(1).row2 := 2;
   v_test_arr(2).row1 := 1;
   v_test_arr(2).row2 := 3;
   v_test_arr(3).row1 := 2;
   v_test_arr(3).row2 := 4;
   v_test_arr(4).row1 := 5;
   v_test_arr(4).row2 := 2;
   v_test_arr(5).row1 := 3;
   v_test_arr(5).row2 := 1;
   v_test_arr(6).row1 := 3;
   v_test_arr(6).row2 := 6;
   v_test_arr(7).row1 := 4;
   v_test_arr(7).row2 := 2;
   v_test_arr(8).row1 := 6;
   v_test_arr(8).row2 := 3;
   v_test_arr(9).row1 := 2;
   v_test_arr(9).row2 := 5;
   v_test_tab.EXTEND (v_test_arr.COUNT);

   FOR i IN 1 .. v_test_arr.COUNT LOOP
     v_test_tab(i)    := test_rec (LEAST(v_test_arr(i).row1, v_test_arr(i).row2), GREATEST(v_test_arr(i).row1, v_test_arr(i).row2));
   END LOOP;

   for rec in (select * from table(cast( SET (v_test_tab) as test_tab))) loop
     dbms_output.put_line(rec.row1||' '||rec.row2);
   end loop;   
END;
 /
Re: Finding distinct elements from Array [message #332375 is a reply to message #331987] Tue, 08 July 2008 06:53 Go to previous messageGo to next message
deepayan
Messages: 51
Registered: December 2005
Member
Thanks for your reply JRowbottom .
Re: Finding distinct elements from Array [message #332381 is a reply to message #332375] Tue, 08 July 2008 07:14 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you may have misundestood - my post was a demonstration of how to use the SET operator to get the unique values - it still uses SQL table types. If you want a purely pl/sql table solution, try my first one - it only needs a single pass through the array to get the unique values, and then probably a pass through the results to get them in whatever table you want them in.
Previous Topic: Maximum size of a record in kb
Next Topic: drop and recreate new partition
Goto Forum:
  


Current Time: Sat Dec 10 20:17:59 CST 2016

Total time taken to generate the page: 0.16771 seconds