Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Multi row subquery in select as array

621934Jun 16 2009 — edited Jun 16 2009
Hi all,
I'm looking for the SQL-syntax to get a subquery with several rows as array in the select-part, so I can read it for example with JDBC by ResultSet.getArray(int).
In PostgreSQL this works with:
SELECT col1, col2, Array(select d1 from t2 where d2=col1), col3 FROM t1;
In Oracle it's possible to get one col and one row by
SELECT c1, c2, (select d1 from t2 where d2=c1), c3 FROM t1;
, but the selection of more than one row returns a ORA-01427 ("single-row subquery returns more than one row").
Because performance is important, slow workarounds are not possible for me.
Thanks!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 14 2009
Added on Jun 16 2009
2 comments
3,527 views