Collect function

#11

Maxim Demenko schreef:

Quote:

ddf wrote:
On Sep 16, 2:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
"Dan Blum" <t... (AT) panix (DOT) com> a écrit dans le message de news:
h8rdtj$e6... (AT) reader1 (DOT) panix.com...| Sashi <small... (AT) gmail (DOT) 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 (DOT) 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

#12

On Sep 16, 3:32*pm, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:

Quote:

ddf wrote:
On Sep 16, 2:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
"Dan Blum" <t... (AT) panix (DOT) com> a écrit dans le message de news: h8rdtj$e6... (AT) reader1 (DOT) panix.com...| Sashi <small... (AT) gmail (DOT) 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 (DOT) 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

#13

On Sep 16, 3:36*pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

Quote:

"Dan Blum" <t... (AT) panix (DOT) com> a écrit dans le message de news: h8rdtj$e6.... (AT) reader1 (DOT) panix.com...| Sashi <small... (AT) gmail (DOT) 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 (DOT) 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

#14

"Sashi" <smalladi (AT) gmail (DOT) com> a écrit dans le message de news: 9bb71fb6-8213-470a-b3ae-3f9494695aed...oglegroups.com...
On Sep 16, 3:36 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

Quote:

"Dan Blum" <t... (AT) panix (DOT) com> a écrit dans le message de news: h8rdtj$e6... (AT) reader1 (DOT) panix.com...| Sashi <small... (AT) gmail (DOT) 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 (DOT) 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Collect function

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Collect function - 09-17-2009 , 12:56 AM

Re: Collect function - 09-17-2009 , 07:39 AM

Re: Collect function - 09-29-2009 , 09:48 AM

Re: Collect function - 09-29-2009 , 10:35 AM