Hi Rob, On Sat, Aug 11, 2018 at 2:33 AM Rob Sargent <[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) > .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]. For more options, visit https://groups.google.com/d/optout.
