Re: Collect function

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 16 Sep 2009 22:32:22 +0200
Message-ID: <4AB14B56.7080906_at_gmail.com>

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 Received on Wed Sep 16 2009 - 15:32:22 CDT