 |
|
| Computers Forum Index » Computer - Databases - Oracle (Misc) » Collect function... |
|
Page 1 of 1 |
|
| Author |
Message |
| Sashi... |
 Posted: Wed Sep 16, 2009 2:47 am |
|
|
|
Guest
|
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 |
|
|
| Back to top |
|
|
|
| ddf... |
| Posted: Wed Sep 16, 2009 11:49 am |
|
|
|
Guest
|
On Sep 15, 9:47 pm, Sashi <small... at (no spam) gmail.com> wrote:
Quote: 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 |
|
|
| Back to top |
|
|
|
| Sashi... |
| Posted: Wed Sep 16, 2009 2:39 pm |
|
|
|
Guest
|
On Sep 16, 7:49 am, ddf <orat... at (no spam) msn.com> wrote:
Quote: On Sep 15, 9:47 pm, Sashi <small... at (no spam) 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 |
|
|
| Back to top |
|
|
|
| Mark D Powell... |
| Posted: Wed Sep 16, 2009 3:37 pm |
|
|
|
Guest
|
On Sep 16, 10:39 am, Sashi <small... at (no spam) gmail.com> wrote:
Quote: On Sep 16, 7:49 am, ddf <orat... at (no spam) msn.com> wrote:
On Sep 15, 9:47 pm, Sashi <small... at (no spam) 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 -- |
|
|
| Back to top |
|
|
|
| Dan Blum... |
| Posted: Wed Sep 16, 2009 7:22 pm |
|
|
|
Guest
|
Sashi <smalladi at (no spam) gmail.com> wrote:
Quote: 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 tool at (no spam) panix.com
"I wouldn't have believed it myself if I hadn't just made it up." |
|
|
| Back to top |
|
|
|
| ddf... |
| Posted: Wed Sep 16, 2009 8:00 pm |
|
|
|
Guest
|
On Sep 16, 2:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
Quote: "Dan Blum" <t... at (no spam) panix.com> a écrit dans le message de news: h8rdtj$e6.... at (no spam) reader1.panix.com...| Sashi <small... at (no spam) 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 (no spam) 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:
SQL> create table employee(dept_id varchar(4), fname varchar(20),
lname
2 varchar(20));
Table created.
SQL>
SQL> insert into employee(dept_id, fname, lname) values ('1', 'John',
2 'Smith');
1 row created.
SQL> insert into employee(dept_id, fname, lname) values ('1', 'Jane',
2 'Smith');
1 row created.
SQL> insert into employee(dept_id, fname, lname) values ('1', 'Harry',
2 'Arnold');
1 row created.
SQL> insert into employee(dept_id, fname, lname) values ('2', 'Sam',
2 'Smith');
1 row created.
SQL> insert into employee(dept_id, fname, lname) values ('2',
'Samantha',
2 'Smith');
1 row created.
SQL> insert into employee(dept_id, fname, lname) values ('2', 'Peter',
2 'Jones');
1 row created.
SQL>
SQL>
SQL> select dept_id, collect(lname) from employee group by dept_id ;
DEPT
----
COLLECT(LNAME)
--------------------------------------------------------------------------------
1
SYSTPdq33znlnSueX+6EjD+ARrA==('Smith', 'Smith', 'Arnold')
2
SYSTPdq33znlnSueX+6EjD+ARrA==('Smith', 'Smith', 'Jones')
SQL> select dept_id, collect(distinct lname) from employee group by
dept_id ;
DEPT
----
COLLECT(DISTINCTLNAME)
--------------------------------------------------------------------------------
1
SYSTPdq33znlnSueX+6EjD+ARrA==('Smith', 'Smith', 'Arnold')
2
SYSTPdq33znlnSueX+6EjD+ARrA==('Smith', 'Smith', 'Jones')
SQL>
which is what the OP wanted. Michel's solution is probably the best
for this situation:
SQL> create or replace type mytype as table of varchar2(30);
2 /
Type created.
SQL>
SQL> select dept_id, set(cast(collect(lname) as mytype)) from employee
group by dept_id ;
DEPT
----
SET(CAST(COLLECT(LNAME)ASMYTYPE))
--------------------------------------------------------------------------------
1
MYTYPE('Smith', 'Arnold')
2
MYTYPE('Smith', 'Jones')
SQL>
David Fitzjarrell |
|
|
| Back to top |
|
|
|
| Michel Cadot... |
| Posted: Wed Sep 16, 2009 8:18 pm |
|
|
|
Guest
|
"Sashi" <smalladi at (no spam) gmail.com> a écrit dans le message de news: 3ee9f014-288a-4aba-bba9-c1828fe8c3db at (no spam) 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 |
|
|
| Back to top |
|
|
|
| Dan Blum... |
| Posted: Wed Sep 16, 2009 8:24 pm |
|
|
|
Guest
|
ddf <oratune at (no spam) msn.com> wrote:
Quote: On Sep 16, 2:36?pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
"Dan Blum" <t... at (no spam) panix.com> a ?crit dans le message de news: h8rdtj$e6... at (no spam) reader1.panix.com...| Sashi <small... at (no spam) 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 (no spam) 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:
Weird. DISTINCT works inside aggregation functions (SELECT COUNT(DISTINCT) GROUP BY
works, for example), so one would expect it to work here.
--
_______________________________________________________________________
Dan Blum tool at (no spam) panix.com
"I wouldn't have believed it myself if I hadn't just made it up." |
|
|
| Back to top |
|
|
|
| Michel Cadot... |
| Posted: Wed Sep 16, 2009 11:36 pm |
|
|
|
Guest
|
"Dan Blum" <tool at (no spam) panix.com> a écrit dans le message de news: h8rdtj$e66$1 at (no spam) reader1.panix.com...
| Sashi <smalladi at (no spam) 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 tool at (no spam) 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 |
|
|
| Back to top |
|
|
|
| Maxim Demenko... |
| Posted: Thu Sep 17, 2009 12:32 am |
|
|
|
Guest
|
ddf wrote:
Quote: On Sep 16, 2:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
"Dan Blum" <t... at (no spam) panix.com> a écrit dans le message de news: h8rdtj$e6... at (no spam) reader1.panix.com...| Sashi <small... at (no spam) 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 (no spam) 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 |
|
|
| Back to top |
|
|
|
| Shakespeare... |
| Posted: Thu Sep 17, 2009 9:56 am |
|
|
|
Guest
|
Maxim Demenko schreef:
Quote: ddf wrote:
On Sep 16, 2:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
"Dan Blum" <t... at (no spam) panix.com> a écrit dans le message de news:
h8rdtj$e6... at (no spam) reader1.panix.com...| Sashi <small... at (no spam) 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 (no spam) 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 |
|
|
| Back to top |
|
|
|
| ddf... |
| Posted: Thu Sep 17, 2009 12:39 pm |
|
|
|
Guest
|
On Sep 16, 3:32 pm, Maxim Demenko <mdeme... at (no spam) gmail.com> wrote:
Quote: ddf wrote:
On Sep 16, 2:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
"Dan Blum" <t... at (no spam) panix.com> a écrit dans le message de news: h8rdtj$e6... at (no spam) reader1.panix.com...| Sashi <small... at (no spam) 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 (no spam) 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 |
|
|
| Back to top |
|
|
|
| Sashi... |
| Posted: Tue Sep 29, 2009 2:48 pm |
|
|
|
Guest
|
On Sep 16, 3:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
Quote: "Dan Blum" <t... at (no spam) panix.com> a écrit dans le message de news: h8rdtj$e6.... at (no spam) reader1.panix.com...| Sashi <small... at (no spam) 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 (no spam) 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
Michel, what version were you using? It doesn't work for me.
----------------------------------------------------------------------------------------------------------------
SQL> create or replace type mytyp as table of integer;
2 /
Type created.
SQL> select set(cast(collect(my_value)))
2 from my_table
3 where rownum<100;
select set(cast(collect(my_value)))
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release
10.2.0.4.0 - 6bit Production
----------------------------------------------------------------------------------------------------------------
Thanks,
Sashi |
|
|
| Back to top |
|
|
|
| Michel Cadot... |
| Posted: Tue Sep 29, 2009 7:35 pm |
|
|
|
Guest
|
"Sashi" <smalladi at (no spam) gmail.com> a écrit dans le message de news: 9bb71fb6-8213-470a-b3ae-3f9494695aed at (no spam) y21g2000yqn.googlegroups.com...
On Sep 16, 3:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
Quote: "Dan Blum" <t... at (no spam) panix.com> a écrit dans le message de news: h8rdtj$e6... at (no spam) reader1.panix.com...| Sashi <small... at (no spam) 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 (no spam) 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
Michel, what version were you using? It doesn't work for me.
----------------------------------------------------------------------------------------------------------------
SQL> create or replace type mytyp as table of integer;
2 /
Type created.
SQL> select set(cast(collect(my_value)))
2 from my_table
3 where rownum<100;
select set(cast(collect(my_value)))
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release
10.2.0.4.0 - 6bit Production
Thanks,
Sashi
-------------------------------------------------------------------------
You didn't say to what you cast.
Regards
Michel |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Sat Dec 12, 2009 5:03 am
|
|