I would have done it :)
On Thursday, August 30, 2012 5:07:10 PM UTC+2, Stanislas Nanchen wrote: > > 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; > >
