Thanks so much for your help.
I had to use the inline() trick.
If I defined the CTE as
CommonTableExpression<?> cte =
name("cte").as(ctx.select(PROBANDSET.ID,
PROBANDSET.NAME,
PROBANDSET.PROBANDS,
PROBANDSET.PEOPLE_ID).from(PROBANDSET).where(PROBANDSET.ID.eq(supersetId)));
OR CommonTableExpression<?> cte =
name("cte").as(ctx.selectFrom(PROBANDSET).where(PROBANDSET.ID.eq(supersetId)));
Field<?>superProbands = cte.field(PROBANDSET.PROBANDS); OR
Field<?>superProbands = cte.field("probands");
I cannot call "contains()" on the superProbands field.
BUT THIS COMPILES
ctx.insertInto(PROBANDSET_GROUP_MEMBER,
PROBANDSET_GROUP_MEMBER.GROUP_ID,
PROBANDSET_GROUP_MEMBER.MEMBER_ID)
.select(ctx.select(pbsgRec.field1(),PROBANDSET.ID)
.from(a)
.where((inline(supersetIds).contains(a.PROBANDS))).and(a.PEOPLE_ID.eq(peepRec.getId())))
.execute();
Thanks again,
rjs
On 08/13/2018 01:42 AM, Lukas Eder wrote:
Hi Rob,
On Sat, Aug 11, 2018 at 2:33 AM Rob Sargent <[email protected]
<mailto:[email protected]>> wrote:
1. I've only seen reference to "contains()" and I would like the
inverse.
Right now, you will need to roll your own using plain SQL templating:
https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating
To make matters worse I would like to use a constant (known
value, local variable) on the left hand side in part because I'm
failing at crafting a sub-select for that single value.
The method you're looking for is called DSL.inline()
https://www.jooq.org/doc/latest/manual/sql-building/bind-values/inlined-parameters/
2. I'm having trouble accessing a CTE: Notice below that the on()
clause is NOT using the CTE. I am unable to get the correct
handle to the CTE. I need to replace the /first/ "a.PROBANDS" with
the single value returned by the CTE.
|
Probandset a = PROBANDSET.as("a");
ctx.with("cte").as(ctx.selectFrom(PROBANDSET).where(PROBANDSET.ID.eq(supersetId)))
.insertInto(PROBANDSET_GROUP_MEMBER,
PROBANDSET_GROUP_MEMBER.GROUP_ID,
PROBANDSET_GROUP_MEMBER.MEMBER_ID)
.select(ctx.select(pbsgRec.field1(),PROBANDSET.ID
<http://PROBANDSET.ID>)
.from("cte").join(a).on(a.PROBANDS.contains(a.PROBANDS)))
.execute();
}
|
You can either use something like field(name("cte",
a.PROBANDS.getName()), a.PROBANDS.getDataType()), or you can assign
your CTE to a local variable:
CommonTableExpression<?> cte = name("cte").as(...);
Field<?> probands = cte.field(a.PROBANDS);
...
In both cases, as always, the following static import is assumed:
import static org.jooq.impl.DSL.*;
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.
