CODEWITHSUNDEEP
javasqloracle-databasejooq
nanpakal
nanpakal

Reputation: 1021

jooq query for sql query with analytical function

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())

Upvotes: 1

Views: 156

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221076

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.

Upvotes: 1

Related Questions