JOOQ : Creating subqueries using WITH clause (using plain SQL)

user_5256567 0
Viewed 227

I am having trouble generating subquery using "WITH" clause with "plain SQL Query". Use case is that I have an SQL and I just want to use it as a with subquery and create a query on top of that.

Example:

Query I have:

"SELECT a,b,c FROM T"

I want to create:

WITH "t1" as ( SELECT a,b,c FROM T ) 
SELECT a,b FROM "t1";

I am trying to use DSL.with(...) but it only takes SelectStep or CTE. I also noticed that we can't create CTE using plain SQL. Please help with this if there is any way to achieve this. Thanks in advance.

jooq with-statement sql java
1 Answers

Starting from jOOQ 3.15, the WithAsStep.as() methods take a ResultQuery as an argument, not just a Select. This means you can wrap a DSL.resultQuery(String) (i.e. a "plain SQL template"). Just write:

ctx.with(name("t1"))
   .as(resultQuery("SELECT a, b, c FROM t"))
   .select(field(unquotedName("a")), field(unquotedName("b")))
   .from(name("t1"))
   .fetch();

Assuming, as always, this static import:

import static org.jooq.impl.DSL.*;
user_5217990
Related