CODEWITHSUNDEEP
sqloracle11g
markmnl
markmnl

Reputation: 11426

Oracle equivalent of PostgreSQL's sub-select to array?

Is there an equivalent in Oracle to get an array from a subselect so if there is more than one row in the subselect's results there will still only be one row in the final results?

e.g.:

select c.name, array(select order_id from ORDER o where o.customer_id = c.id)
from CUSTOMER c;

Will only return one row per CUSTOMER, where the second value in each returned row is an array of order_id's.

Upvotes: 1

Views: 999

Answers (1)

markmnl
markmnl

Reputation: 11426

You can use a CURSOR:

select c.name, cursor(select order_id from ORDER o where o.customer_id = c.id)
from CUSTOMER c;

Then your database interface will have some way of getting the results out of a cursor result.

Upvotes: 1

Related Questions