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. Chen Shapira

    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. Laurent Schneider Post author

    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. Madhu

    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. Madhu

    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. Madhu

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

    collect (column)

Comments are closed.