select distinct collect

I answered a question on otn today about distinct.

Reprased, how to select distinct collection?


select job, collect(distinct deptno) deptnos 
from emp group by job;

JOB       DEPTNOS
--------- -------------------------------------------
ANALYST   SYSTPTJCzBffh0AjgQ59n0o3QCA==(20)
CLERK     SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30)
MANAGER   SYSTPTJCzBffh0AjgQ59n0o3QCA==(10, 20, 30)
PRESIDENT SYSTPTJCzBffh0AjgQ59n0o3QCA==(10)
SALESMAN  SYSTPTJCzBffh0AjgQ59n0o3QCA==(30)

5 rows selected.

select distinct collect(distinct deptno) deptnos 
from emp group by job;

ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method

the message is clear, i need a MAP or ORDER method. Ok, I can do this


create type tt_n as table of number(2);
/

Type created.

create type tt_n_ord as object(
  c tt_n,
  order member function 
    eq (tc tt_n_ord) return integer);
/

Type created.

create type body tt_n_ord as
  order member function 
    eq (tc tt_n_ord) return integer
  is
  begin 
    if c=tc.c 
    then return 0; 
    else return 1; 
    end if;
  end;
end;
/

Type body created.

select distinct 
  tt_n_ord(cast(collect(distinct deptno) as tt_n))
from emp group by job;

TT_N_ORD(CAST(COLLECT(DISTINCTDEPTNO)ASTT_N))(C)
------------------------------------------------
TT_N_ORD(TT_N(30))
TT_N_ORD(TT_N(10))
TT_N_ORD(TT_N(10, 20, 30))
TT_N_ORD(TT_N(20))

4 rows selected.

7 thoughts on “select distinct collect”

  1. Maybe its still too early in the morning, but:

    select job, collect(distinct deptno) deptnos
    from emp group by job;

    works.

    Same query, remove one column and you get an error about order:

    select distinct collect(distinct deptno) deptnos
    from emp group by job;

    ERROR at line 1:
    ORA-22950: cannot ORDER objects without MAP or ORDER method

    How does that make sense?

    (BTW. I understood the question as STRAGG question, but as I said, its still early)

  2. too early

    select job, collect(distinct deptno) deptnos
    from emp group by job;

    works

    but

    select collect(distinct deptno) deptnos
    from emp group by job;

    works

    but not with distinct — !-!!

  3. On a side issue, if you look at the spec for collect in sql reference, it is defined as:

    
    COLLECT (column)
    

    So it appears , collect (distinct column) should be a syntax error , but is not. We have a code that uses:
    
    COLLECT (col1 order by col2)
    

    It works in general but we have run into ORA-00600 when used from a java application and have a open SR. I am not sure if we got lucky into using “order by” or if it’s a documentation bug.
    Any thoughts?

    thanks.

  4. For some reason a line is missing in my previous posting and the format is messed too.

    The sql reference spec for collect is defined as:

    (the missing line)

    
    COLLECT (column)
    

  5. It’s not showing up once again, wish there was preview. :-(
    Last try:

    collect (column)

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>