Obviously both occurrences of “is exactly” should have been “isn’t exactly”
> On Mar 17, 2017, at 9:18 AM, Rob Sargent <[email protected]> wrote: > > Of course both suggestions worked, thank you very much. > > As to whether your second suggestion is confusing I would say No, not more so > than the first. In the first (shown in your correction) aliasing the derived > table, which has one column ("id") as "person" to enable the aggregate to see > an "id" column is exactly straight forward :). > > The second choice is exactly straight forward either, as you have to play > tricks at both ends of the query, but maybe the ending of "asTable("id") is a > better match for what one might do at the sql prompt? > > But of these both seem like tricks to get to stings/names, so why doesn't > "arrayAgg("id")" work since the expression amounts to > > array_agg(id) from (select id from person where... ) > > Thanks ever so much, as always > rjs > > > On 03/17/2017 07:25 AM, Lukas Eder wrote: >> Try this instead: >> >> 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).asTable(PERSON.getName()))) >> .execute(); >> >> The explanation is simple: Your derived table (select in FROM clause) needs >> an alias. That alias is now PERSON. The only reasonable alias if you want to >> reference the PERSON.ID from the outer table. >> >> In your example, you renamed the table to "id", which means, you'd have to >> reference an "id".ID column in the outer query. That's possible too, but >> perhaps a bit confusing? >> >> arrayAgg(PERSON.as("id").ID) >> >> Hope this helps, >> Lukas >> -- >> 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.
