Re: Collect function

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 16 Sep 2009 08:37:27 -0700 (PDT)
Message-ID: <e420f4dd-06c5-44f8-8195-7107dcf9767e_at_d23g2000vbm.googlegroups.com>

On Sep 16, 10:39 am, Sashi <small..._at_gmail.com> wrote:
> On Sep 16, 7:49 am, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On Sep 15, 9:47 pm, 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.
>
> > > Thanks,
> > > Sashi
>
> > Provide some sample data so we can test this ourselves.
>
> > David Fitzjarrell
>
> DDL:
> create table employee(dept_id varchar(4), fname varchar(20), lname
> varchar(20));
>
> DML:
> insert into employee(dept_id, fname, lname) values ('1', 'John',
> 'Smith');
> insert into employee(dept_id, fname, lname) values ('1', 'Jane',
> 'Smith');
> insert into employee(dept_id, fname, lname) values ('1', 'Harry',
> 'Arnold');
> insert into employee(dept_id, fname, lname) values ('2', 'Sam',
> 'Smith');
> insert into employee(dept_id, fname, lname) values ('2', 'Samantha',
> 'Smith');
> insert into employee(dept_id, fname, lname) values ('2', 'Peter',
> 'Jones');
>
> Fetch:
> select dept_id, collect(lname) from employee group by dept_id
>
> Result:
>
> DEPT, COLLECT(LNAME)
> ---------------------------------------------------------------------------­-----
> 1, SYSTPc7POHe3EMMDgRAAAvqlgUw==('Smith', 'Smith', 'Arnold')
>
> 2, SYSTPc7POHe3EMMDgRAAAvqlgUw==('Smith', 'Smith', 'Jones')
>
> The collect function 'collects' requested values and returns it as a
> collection. It, however, does store dups. I'd like to avoid the dups.
>
> Googling around didn't help much (or I did a bad job of it).
>
> Does anyone know how to restrict the collection to distinct values?
> collect(distinct lname) doesn't help.
>
> TIA,
> Sashi- Hide quoted text -
>
> - Show quoted text -

In your sample data is SMITH really a duplicate or should you be collecting the combination of LNAME||','||FNAME ?

I ask because the multiple SMITH's are not really the same rows though you may not care.

I just hate to see someone spend effort on solving the wrong issue.

HTH -- Mark D Powell -- Received on Wed Sep 16 2009 - 10:37:27 CDT