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

Beginning with jOOQ 3.17, the Situation kind extends the Subject<Boolean> kind. As a result of, that’s what the SQL customary thinks it’s, in types:

<boolean worth expression> ::=
  <predicate>

The precise definition comprises intermediate guidelines, however you get the thought. A <predicate> (which is a Situation in jOOQ) can be utilized wherever a <boolean worth expression> can be utilized, which once more can be utilized in projections, predicates, and elsewhere.

Not all SQL dialects work this fashion, and actually, earlier than SQL:1999 standardised on the BOOLEAN knowledge kind, SQL itself didn’t work this fashion. SQL-92, for instance, listed <predicate> as a doable substitute for the <search situation> solely, which is used for instance in <the place clause>, however not in any abnormal <worth expression>.

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

SELECT id, id > 2 AS big_id
FROM ebook
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 same old 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 all the time supported a approach to make use of Situation and Subject<Boolean> exchangeably. There are the 2 wrapper strategies:

• DSL.area(Situation) returns Subject<Boolean>
• DSL.situation(Subject<Boolean>) returns Situation

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

Outcome<Record2<Integer, Boolean>> end result =
ctx.choose(BOOK.ID, area(BOOK.ID.gt(2)).as("big_id"))
//                  ^^^^^^^^^^^^^^^^^^^^ wrapping situation with area()
   .from(BOOK)
   .orderBy(BOOK.ID)
   .fetch();

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

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

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

SELECT
  ebook.id,
  CASE
    WHEN ebook.id > 2 THEN 1
    WHEN NOT (ebook.id > 2) THEN 0
  END big_id
FROM ebook
ORDER BY ebook.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 area(Situation) is now not obligatory, and you may simply undertaking the Situation instantly:

Outcome<Record2<Integer, Boolean>> end result =
ctx.choose(BOOK.ID, BOOK.ID.gt(2).as("big_id"))
   //               ^^^^^^^^^^^^^ no extra wrapping obligatory
   .from(BOOK)
   .orderBy(BOOK.ID)
   .fetch();

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

• GROUP BY or PARTITION BY
• ORDER BY

Time to improve your jOOQ model!