Prev: Oracle9i Client - where is it ? ....
Next: Using a case within a simple update
From: Sashi on 15 Sep 2009 22:47
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
From: ddf on 16 Sep 2009 07:49
On Sep 15, 9:47 pm, Sashi <small...(a)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
From: Sashi on 16 Sep 2009 10:39
On Sep 16, 7:49 am, ddf <orat...(a)msn.com> wrote:
> On Sep 15, 9:47 pm, Sashi <small...(a)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



From: Mark D Powell on 16 Sep 2009 11:37
On Sep 16, 10:39 am, Sashi <small...(a)gmail.com> wrote:
> On Sep 16, 7:49 am, ddf <orat...(a)msn.com> wrote:
>
>
>
>
>
> > On Sep 15, 9:47 pm, Sashi <small...(a)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 --


From: Michel Cadot on 16 Sep 2009 12:18

"Sashi" <smalladi(a)gmail.com> a �crit dans le message de news: 3ee9f014-288a-4aba-bba9-c1828fe8c3db(a)p15g2000vbl.googlegroups.com...
| 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

SQL> create table t (val integer);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> insert into t values (2);

1 row created.

SQL> insert into t values (1);

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select collect(val) from t;
COLLECT(VAL)
-----------------------------------------------
SYSTPpPD1MGj3Qdyoo0Jt2E7Lhg==(1, 2, 1, 1)

1 row selected.

You can use SET operator it is made for this.
Unfornatunatly, at least in 10.2.0.4, it does not directly work
on collection generated by COLLECT.

SQL> select set(collect(val)) from t;
select set(collect(val)) from t
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected UDT got -

You have to create your own same datatype:


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

Type created.

SQL> select set(cast(collect(val) as mytyp)) from t;
SET(CAST(COLLECT(VAL)ASMYTYP))
-------------------------------------------------------
MYTYP(1, 2)

1 row selected.

Regards
Michel




 |  Next  |  Last
Pages: 1 2 3
Prev: Oracle9i Client - where is it ? ....
Next: Using a case within a simple update