Hi! I'm currently trying to reproduce in jOOQ some of the queries we have in a big project and i am trying to find out how to write a cast(multiset(<Select>) as <type>) query. We use these kinds of queries to retrieve parent rows with arrays of children rows.
How do I do that? Thanks for your help. stan. example: create table A ( id number, primary key (id)); create table B ( a_id number, seq number, primary key(a_id, seq), foreign key(a_id) references A(id) ); create or replace type OBJECT_B is object( a_id number, seq number ); / create or replace type ARRAY_B is table of OBJECT_B; / insert into a values (1); insert into b values (1, 1); insert into b values (1, 2); commit; select a.id, cast(multiset(select * from b where b.a_id = a.id) as ARRAY_B) from a;
