Hi lukas, Actually, i have a parent table with 5 children tables and lots of records that i must stream to an XML file. with a big "select id, cast(multiset), cast(multiset), .., cast(multiset)" i can stream from record to record without having to cache or to make "buckets" of 1000. it works quite well.
cheers. stan. 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; > >
