pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOT: array_accum equivalent in Oracle
4+ messages / 2 participants
[nested] [flat]
* OT: array_accum equivalent in Oracle
@ 2007-10-12 14:16 Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas Joseph Krogh @ 2007-10-12 14:16 UTC (permalink / raw)
To: pgsql-sql
WARNING: The rest of this post is for somebody who has worked with Oracle and
has migrated to PG, or for some other reason has good experience with Oracle.
I know this is off-topic for this list and should be asked in an Oracle
support-forum, but I don't have access to that, and Uncle Google didn't
return any obvious results, so here it goes...
Anybody knows if Oracle has an equivalent of PG's array_accum or
ARRAY(subselect) construct?
I need a way to accomplish this in Oracle:
select ARRAY(select t.id from table t where t.user_id = u.id) as
id_array, ....
Any hints on where to look in Oracle-docs are welcome.
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: OT: array_accum equivalent in Oracle
@ 2007-10-12 15:02 Jonah H. Harris <jonah.harris@gmail.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 1 reply; 4+ messages in thread
From: Jonah H. Harris @ 2007-10-12 15:02 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
On 10/12/07, Andreas Joseph Krogh <andreak@officenet.no> wrote:
> Anybody knows if Oracle has an equivalent of PG's array_accum or
> ARRAY(subselect) construct?
Something like this:
CREATE TYPE varchar2_table_t AS TABLE OF VARCHAR2(32767);
SELECT job, CAST(MULTISET(SELECT ename FROM emp WHERE job = e.job) AS
varchar2_table_t) FROM emp e GROUP BY job;
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: OT: array_accum equivalent in Oracle
@ 2007-10-12 15:43 Andreas Joseph Krogh <andreak@officenet.no>
parent: Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas Joseph Krogh @ 2007-10-12 15:43 UTC (permalink / raw)
To: pgsql-sql; +Cc: Jonah H. Harris <jonah.harris@gmail.com>
On Friday 12 October 2007 17:02:23 Jonah H. Harris wrote:
> On 10/12/07, Andreas Joseph Krogh <andreak@officenet.no> wrote:
> > Anybody knows if Oracle has an equivalent of PG's array_accum or
> > ARRAY(subselect) construct?
>
> Something like this:
>
> CREATE TYPE varchar2_table_t AS TABLE OF VARCHAR2(32767);
> SELECT job, CAST(MULTISET(SELECT ename FROM emp WHERE job = e.job) AS
> varchar2_table_t) FROM emp e GROUP BY job;
Amazing! Works like a charm.
I was envisioning some pretty ugly PL/SQL functions to accomplish this.
Thanks!
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: OT: array_accum equivalent in Oracle
@ 2007-10-12 15:55 Jonah H. Harris <jonah.harris@gmail.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 0 replies; 4+ messages in thread
From: Jonah H. Harris @ 2007-10-12 15:55 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
On 10/12/07, Andreas Joseph Krogh <andreak@officenet.no> wrote:
> > Something like this:
> >
> > CREATE TYPE varchar2_table_t AS TABLE OF VARCHAR2(32767);
> > SELECT job, CAST(MULTISET(SELECT ename FROM emp WHERE job = e.job) AS
> > varchar2_table_t) FROM emp e GROUP BY job;
>
> Amazing! Works like a charm.
> I was envisioning some pretty ugly PL/SQL functions to accomplish this.
>
> Thanks!
No problem.
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-10-12 15:55 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-12 14:16 OT: array_accum equivalent in Oracle Andreas Joseph Krogh <andreak@officenet.no>
2007-10-12 15:02 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-10-12 15:43 ` Andreas Joseph Krogh <andreak@officenet.no>
2007-10-12 15:55 ` Jonah H. Harris <jonah.harris@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox