Hi Rob, Thanks for your enquiry. Interesting! Can you point me to the doc page where this ORDER BY clause is documented, I somehow cannot seem to find it. I wasn't aware of this syntax possibility.
The error you're getting is because jOOQ always generates an automatic alias (in your case "alias_49688522") for your derived tables, because that's a syntactic requirement in most databases. In that sense, the "person" table ceases to exist syntactically outside of your derived table. So, neither your ARRAY_AGG() operation, nor your ORDER BY clause can make use of the qualified PERSON.ID column. You have two options: 1) Use an unqualified reference to the column (e.g. using plain SQL) 2) provide an explicit alias to your derived table: "person", e.g. by using table(Select).as(PERSON), or select.asTable(PERSON) Hope this helps, Lukas 2017-03-13 23:58 GMT+01:00 Rob Sargent <[email protected]>: > 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. > -- 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.
