Multi row subquery in select as array
621934Jun 16 2009 — edited Jun 16 2009Hi 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!