CODEWITHSUNDEEP
javajooq
Ivancodescratch
Ivancodescratch

Reputation: 405

Alias with two Fields using JOOQ

i have a nasty SQl that i want transform in JOOQ

Here are the Query:

  SELECT 
    SUM(dpr.dpr_bruttopraemie_prt + dpr.dpr_sofortrabatt_prt) 
    , MAX(TO_NUMBER(DP1.dp_wert))  
      FROM deckungen deck, deckungspraemien dpr, 
        (SELECT dp.dp_id, dp.dp_wert 
              FROM  textbausteine txb, druckparameter dp 
              WHERE  dp.dp_txb_id = txb.txb_id 
              ) DP1 
      WHERE DP1.dp_id = :druckparameter_id;

As you can see, i need to make alias from a select with two Fields.

dp.dp_id, dp.dp_wert

that im going to used it on some other parts.

How i can i get it done?

i've seen

.asField()

Funktion but it only make alias for one column.

PS: The actual Query are a lot more complicated. So i wrote a simpler one. With hoping that it's satisfied the SQL ORACLE Dialect.

Upvotes: 1

Views: 1818

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

I'm assuming that you're using the code generator, so you have generated objects available for your tables like DECKUNGEN. I'm also assuming you're using these static imports:

import static org.jooq.impl.DSL.*;                  // The jOOQ API
import static com.example.myapp.generated.Tables.*; // Your generated tables

You can then write:

Deckungen deck = DECKUNGEN.as("deck");
Deckungspraemien dpr = DECKUNGSPRAEMIEN.as("dpr");
Textbausteine txb = TEXTBAUSTEINE.as("txb");
Druckparameter dp = DRUCKPARAMETER.as("dp");

Table<?> dp1 = table(
    select(dp.DP_ID, dp.DP_WERT)
   .from(txb, dp)
   .where(dp.DP_TXB_ID.eq(txb.TXB_ID))
).as("dp1");

Record2<BigDecimal, BigDecimal> result =
using(configuration)
  .select(
     sum(dpr.DPR_BRUTTOPRAEMIE_PRT.plus(dpr.DPR_SOFORTRABATT_PRT)),
     max(field("to_number({0})", BigDecimal.class, dp1.field(dp.DP_WERT))))
  .from(deck, dpr, dp1)
  .where(dp1.field(dp.DP_ID).eq(druckparameterId))
  .fetchOne();

Some explanations

  • jOOQ currently doesn't have a built-in TO_NUMBER() function, but you can easily roll your own using DSL.field(String) and similar overloads. For more detail, refer to the manual's section about plain SQL
  • A derived table can be created most easily by using the DSL.table(Select) operator.
  • Columns from a derived table can be dereferenced using Table.field() methods, in particular the Table.field(Field), which tries to find a field by the same name as the argument field, retaining the argument field's type information.

Side-note

I don't think your query is correct as you're simply creating cartesian products between your tables deck, dpr, and dp1. Specifically, the SUM() is quite likely to be wrong, whereas MAX() is simply calculated inefficiently.

Upvotes: 2

Related Questions