Re: Collect function

From: Sashi <smalladi_at_gmail.com>
Date: Tue, 29 Sep 2009 07:48:08 -0700 (PDT)
Message-ID: <9bb71fb6-8213-470a-b3ae-3f9494695aed_at_y21g2000yqn.googlegroups.com>

On Sep 16, 3:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "Dan Blum" <t..._at_panix.com> a écrit dans le message de news: h8rdtj$e6..._at_reader1.panix.com...| Sashi <small..._at_gmail.com> wrote:
>
> | > 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.
> |
> | select collect(distinct a1)... should work.
> |
> | --
> | _______________________________________________________________________
> | Dan Blum          t..._at_panix.com
> | "I wouldn't have believed it myself if I hadn't just made it up."
>
> It does.
>
> SQL> select collect(val) from t;
> COLLECT(VAL)
> ---------------------------------------------
> SYSTPYmvHKMavRw27KIB3yptjFg==(1, 2, 1, 1)
>
> 1 row selected.
>
> SQL> select collect(distinct val) from t;
> COLLECT(DISTINCTVAL)
> ---------------------------------------------
> SYSTPYmvHKMavRw27KIB3yptjFg==(1, 2)
>
> 1 row selected.
>
> Regards
> Michel

Michel, what version were you using? It doesn't work for me.

---

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

Type created.

SQL> select set(cast(collect(my_value)))   2 from my_table
  3 where rownum<100;
select set(cast(collect(my_value)))

                              *

ERROR at line 1:
ORA-00905: missing keyword

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 6bit Production

---

Thanks,
Sashi Received on Tue Sep 29 2009 - 09:48:08 CDT