I'm having a hard time constructing an ordered array using version 3.9
on postgres 9.6. I can't seem to get the equivalent of either of these
approaches
select (person.id order by person.id) from person where person.name
in (<list of names>);
select array_agg(s.id) from (select p.id from seg.person as p where
p.name in (<list of names>) order by p.id) as s;
This compiles
ctx.insertInto(PROBANDSET)
.set(PROBANDSET.ID, newsetId)
.set(PROBANDSET.NAME, pedfile.getName())
.set(PROBANDSET.PROBANDS,
ctx.select(arrayAgg(PERSON.ID)).from(ctx.select(PERSON.ID).from(PERSON).where(PERSON.NAME.in(idList)).orderBy(PERSON.ID)))
.execute();
but generates a runtime sql message
org.jooq.exception.DataAccessException: SQL [insert into
"seg"."probandset" ("id", "name", "probands") values (?, ?, (select
array_agg("seg"."person"."id") from (select "seg"."person"."id" from
"seg"."person" where "seg"."person"."name" in (?, ?, ?, ?, ?, ?, ?,
?) order by "seg"."person"."id" asc) as "alias_49688522"))]; ERROR:
missing FROM-clause entry for table "person"
Position: 93
and I can't find the right spot to add another '.from(PERSON)'.
Any help appreciated
--
You received this message because you are subscribed to the Google Groups "jOOQ User
Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
