A Situation is a Subject – Java, SQL and jOOQ.

Therefore, whereas this works in PostgreSQL, which helps customary SQL BOOLEAN varieties:

SELECT id, id > 2 AS big_id
FROM guide
ORDER BY id

Producing:

|id |big_id|
|---|------|
|1  |false |
|2  |false |
|3  |true  |
|4  |true  |

It doesn’t work in Oracle, for instance, which delights us with the standard helpful error message:

SQL Error [923] [42000]: ORA-00923: FROM key phrase not discovered the place anticipated

How this used to work in jOOQ 3.16 or much less

jOOQ has at all times supported a manner to make use of Situation and Subject exchangeably. There are the 2 wrapper strategies:

• DSL.subject(Situation) returns Subject
• DSL.situation(Subject) returns Situation

That is documented right here. As such, the earlier question might have been written as follows:

Consequence> consequence =
ctx.choose(BOOK.ID, subject(BOOK.ID.gt(2)).as("big_id"))
//                  ^^^^^^^^^^^^^^^^^^^^ wrapping situation with subject()
   .from(BOOK)
   .orderBy(BOOK.ID)
   .fetch();

The generated SQL seems to be like this, for PostgreSQL:

SELECT
  guide.id,
  (guide.id > 2) AS big_id
FROM guide
ORDER BY guide.id

And for Oracle, that is the emulation of the characteristic:

SELECT
  guide.id,
  CASE
    WHEN guide.id > 2 THEN 1
    WHEN NOT (guide.id > 2) THEN 0
  END big_id
FROM guide
ORDER BY guide.id

The emulation preserves our beloved three valued logic, i.e. the BOOLEAN worth is NULL in case BOOK.ID is NULL.

How this works in jOOQ 3.17, now

Ranging from jOOQ 3.17 and #11969, this guide wrapping of subject(Situation) is now not crucial, and you may simply challenge the Situation straight:

Consequence> consequence =
ctx.choose(BOOK.ID, BOOK.ID.gt(2).as("big_id"))
   //               ^^^^^^^^^^^^^ no extra wrapping crucial
   .from(BOOK)
   .orderBy(BOOK.ID)
   .fetch();

The behaviour is precisely the identical as should you had wrapped the situation (together with the consequence kind), and the emulation nonetheless kicks in additionally for Oracle and different dialects that don’t assist BOOLEAN worth expressions. This implies you can too use Situation in different clauses that take Subject varieties, together with, e.g.:

• GROUP BY or PARTITION BY
• ORDER BY

Time to improve your jOOQ model!