Re: Collect function

From: ddf <oratune_at_msn.com>
Date: Thu, 17 Sep 2009 05:39:45 -0700 (PDT)
Message-ID: <34fbe651-aff9-4edd-b3b7-91f61201a42d_at_q14g2000vbi.googlegroups.com>

On Sep 16, 3:32 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

I ran this test on 11gR1 so the problem isn't corrected until 11.2.

David Fitzjarrell Received on Thu Sep 17 2009 - 07:39:45 CDT