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 <http://probandset.id/>, newsetId)
.set(PROBANDSET.NAME <http://probandset.name/>,
pedfile.getName())
.set(PROBANDSET.PROBANDS,
ctx.select(arrayAgg(PERSON.ID
<http://person.id/>)).from(ctx.select(PERSON.ID <http://person.id/>)
.from(PERSON)
.where(PERSON.NAME.in <http://person.name.in/>(idList))
.orderBy(PERSON.ID <http://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 <http://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]
<mailto:[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.
