Re: Collect function

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 17 Sep 2009 07:56:04 +0200
Message-ID: <4ab1cf7a$0$83238$e4fe514c_at_news.xs4all.nl>

Maxim Demenko schreef:

> ddf wrote:
>> On Sep 16, 2: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

>>
>> But not with a GROUP BY query:
> 
> Seems to be fixed in 11gR2 (don't have 11gR1 by hand now)
> For 10gR2 this (in my opinion buggy) behaviour can be workarounded ( 
> besides using sql types) using an inline view returning distinct set of 
> rows
> 
> Best regards
> 
> Maxim

But you may run out of memory very quickly (have seen this repeatedly in 10g)

Shakespeare Received on Thu Sep 17 2009 - 00:56:04 CDT