jooq query for sql query with analytical function

Tags: , , ,



i have sql query like

select *
from (
   select s.*, max(year) over () max_year
   from Transaction s
   where s.Account_Number     = '4234242234'
     and s.month in (0,1)) o
where o.year=o.max_year;

i need to write in jOOQ, any help. I tried the below

dslContext.select(Transaction.BANK,
                    Transaction.ACCOUNT,
                    Transaction.AMOUNT,
                    max(Transaction.YEAR).over())
            .from(Transaction)
            .where(Transaction.BANK.eq(bank))
            .and(Transaction.MONTH.in((byte) 0, (byte) 1))
            .fetchInto(Transaction.class);

Also,is there any way to avoid listing all columns in select. Instead of

select(Transaction.BANK,
       Transaction.ACCOUNT,
       Transaction.AMOUNT,
       max(Transaction.YEAR).over())

Something like

select(Transaction.*,
       max(Transaction.YEAR).over())

Answer

A derived table can be constructed like this:

// Assuming this static import, as always:
import static org.jooq.impl.DSL.*;

// Create aliased column expression for later reuse
Field<Integer> maxYear = max(Transaction.YEAR).over().as("max_year");

// Create derived table
Table<?> o = select(Transaction.asterisk(), maxYear)
            .from(Transaction)
            .where(Transaction.BANK.eq(bank))
            .and(Transaction.MONTH.in((byte) 0, (byte) 1))
            .asTable("o");

// Use derived table and dereference fields from it using Table.field(Field)
ctx.select()
   .from(o)
   .where(o.field(Transaction.YEAR).eq(o.field(maxYear)))
   .fetch();

Also, is there any way to avoid listing all columns in select

You’re looking for Table.asterisk() to produce the asterisk in SQL, or Table.fields() to produce all fields from your generated code.



Source: stackoverflow