Categories
Blogroll sql

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.

By Laurent Schneider

Oracle Certified Master

7 replies on “select distinct collect”

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)

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 — !-!!

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.

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)

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.