Collect function
in [Oracle Help]
|
From: Shakespeare on 17 Sep 2009 01:56 Maxim Demenko schreef: > ddf wrote: >> On Sep 16, 2:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote: >>> "Dan Blum" <t...(a)panix.com> a �crit dans le message de news: >>> h8rdtj$e6...(a)reader1.panix.com...| 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. >>> | >>> | select collect(distinct a1)... should work. >>> | >>> | -- >>> | >>> _______________________________________________________________________ >>> | Dan Blum t...(a)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
From: ddf on 17 Sep 2009 08:39 On Sep 16, 3:32 pm, Maxim Demenko <mdeme...(a)gmail.com> wrote: > ddf wrote: > > On Sep 16, 2:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > >> "Dan Blum" <t...(a)panix.com> a écrit dans le message de news: h8rdtj$e6...(a)reader1.panix.com...| 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. > >> | > >> | select collect(distinct a1)... should work. > >> | > >> | -- > >> | _______________________________________________________________________ > >> | Dan Blum t...(a)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
From: Sashi on 29 Sep 2009 10:48 On Sep 16, 3:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > "Dan Blum" <t...(a)panix.com> a écrit dans le message de news: h8rdtj$e6....(a)reader1.panix.com...| 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. > | > | select collect(distinct a1)... should work. > | > | -- > | _______________________________________________________________________ > | Dan Blum t...(a)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
From: Michel Cadot on 29 Sep 2009 11:35 "Sashi" <smalladi(a)gmail.com> a �crit dans le message de news: 9bb71fb6-8213-470a-b3ae-3f9494695aed(a)y21g2000yqn.googlegroups.com... On Sep 16, 3:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > "Dan Blum" <t...(a)panix.com> a �crit dans le message de news: h8rdtj$e6...(a)reader1.panix.com...| 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. > | > | select collect(distinct a1)... should work. > | > | -- > | _______________________________________________________________________ > | Dan Blum t...(a)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
First
|
Prev
|
Pages: 1 2 3 Prev: Oracle9i Client - where is it ? .... Next: Using a case within a simple update |