How can I execute the command in JOOQ with double select

user_15392934 0
Viewed 56

I'm using code nerator!

I need to execute the following query:

SELECT sbcm_ref.process_time_reports_direct_buf_record((SELECT tr.time_reports_buf_id
FROM sbcm_buf.time_reports_direct_buf tr ORDERBY time_reports_buf_id desc limit 1

and this my command

DSL.using(connection).select(Routines.processTimeReportsDirectBufRecord(select(TIME_REPORTS_DIRECT_BUF.TIME_REPORTS_BUF_ID).from(TIME_REPORTS_DIRECT_BUF).orderBy(TIME_REPORTS_DIRECT_BUF.TIME_REPORTS_BUF_ID.desc()).limit(1).fetch())).fetch();

where is mistake?

Thank you!

jooq
1 Answers

You can turn any Select<R extends Record1<T>> into a Field<T> by wrapping it with DSL.field(Select):

ctx.select(Routines.processTimeReportsDirectBufRecord(field(
    select(TIME_REPORTS_DIRECT_BUF.TIME_REPORTS_BUF_ID)
    .from(TIME_REPORTS_DIRECT_BUF)
    .orderBy(TIME_REPORTS_DIRECT_BUF.TIME_REPORTS_BUF_ID.desc())
    .limit(1)
))).fetch();

See also the manual section about scalar subqueries. However, in this particular case, you don't need it. Why not just write:

ctx.select(Routines.processTimeReportsDirectBufRecord(
        max(TIME_REPORTS_DIRECT_BUF.TIME_REPORTS_BUF_ID)
    ))
   .from(TIME_REPORTS_DIRECT_BUF)
   .fetch();
user_5217990
Related