awkward predicates using fields from a CTE

[Steve Ash]

I'm using a CTE and in my main query I need to include a predicate in the where clause comparing a value from the projection 

CommonTableExpression<Record4<Integer, Long, BigDecimal, Integer>> cte = name("rnkd")

    .fields("rt", "acct", "bal", "rnk")

    .as(select(a.TRANSIT, a.ACCT, a.REP_BOD_LEDGER_BALANCE.as("bal"),

               rank().over().partitionBy(a.TRANSIT, a.ACCT).orderBy(a.BALANCE_DATE.desc()))

        .from(a)

    );

    create.with(cte)

         .select(cte.field("rt"), cte.field("acct"), cte.field("bal"))

         .from(cte)

         .where(cte.field("rnk").eq(1L))

          .fetch();

But I cant do that because cte.field("rnk") returns Field<?>.  I was hoping to see an overload where I could pass in the type so I could write the predicate like: 

cte.field("rnk", Long.class).eq(1L)

But no luck there either.  Do I have any better option other than just doing: DSL.field("rnk", Long.class).eq(1L) ?  

It's not a big deal to call field directly -- I just figured it might be convenient to have a field overload to pass in the type.  Are there any risks here? I guess its a CTE so its not like a quoted table where I care about the fully qualified name...

Steve

[Lukas Eder]

Hi Steve,

You could call 

cte.field("rnk").coerce(Long.class).eq(1L)

I like your idea, though:

cte.field("rnk", Long.class).eq(1L)

I suspect it doesn't hurt to add that method to TableLike as a convenience method for the call chain that I've suggested. In particular, there is already a <T> Field<T> field(Field<T>) method that takes a Field<T> as argument and uses both name and type of the argument field to produce the CTE's (or any other table's) field.

We'll add your suggestion to jOOQ 3.6:

https://github.com/jOOQ/jOOQ/issues/4152

I 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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.