Re: Collect function

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 16 Sep 2009 18:18:49 +0200
Message-ID: <4ab10fe9$0$7783$426a74cc_at_news.free.fr>

"Sashi" <smalladi_at_gmail.com> a écrit dans le message de news: 3ee9f014-288a-4aba-bba9-c1828fe8c3db_at_p15g2000vbl.googlegroups.com...
| HI all, I'm trying to use the collect() function (mentioned in my
| earlier post in this ng) but it, well, collects all values. Is there
| any way of avoiding duplicates?
| What I'm trying to do is this:
|
| select collect(a1), a2, a3, a4
| from A
| group by a2, a3, a4.
|
| Thanks,
| Sashi

SQL> create table t (val integer);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> insert into t values (2);

1 row created.

SQL> insert into t values (1);

1 row created.

SQL> / 1 row created.

SQL> commit;

Commit complete.

SQL> select collect(val) from t;
COLLECT(VAL)

---

SYSTPpPD1MGj3Qdyoo0Jt2E7Lhg==(1, 2, 1, 1)

1 row selected.

You can use SET operator it is made for this. Unfornatunatly, at least in 10.2.0.4, it does not directly work on collection generated by COLLECT.

SQL> select set(collect(val)) from t;
select set(collect(val)) from t

           *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got -

You have to create your own same datatype:

SQL> create or replace type mytyp as table of integer;   2 /

Type created.

SQL> select set(cast(collect(val) as mytyp)) from t; SET(CAST(COLLECT(VAL)ASMYTYP))

---

MYTYP(1, 2) 1 row selected.

Regards
Michel Received on Wed Sep 16 2009 - 11:18:49 CDT