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

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

<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 manner, and actually, earlier than SQL:1999 standardised on the BOOLEAN information kind, SQL itself didn’t work this manner. SQL-92, for instance, listed <predicate> as a attainable substitute for the <search situation> solely, which is used for instance in <the place clause>, however not in any peculiar <worth expression>.

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

SELECT id, id > 2 AS big_id
FROM e-book
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 means to make use of Situation and Discipline<Boolean> exchangeably. There are the 2 wrapper strategies:

• DSL.subject(Situation) returns Discipline<Boolean>
• DSL.situation(Discipline<Boolean>) returns Situation

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

Outcome<Record2<Integer, Boolean>> 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 appears to be like like this, for PostgreSQL:

SELECT
  e-book.id,
  (e-book.id > 2) AS big_id
FROM e-book
ORDER BY e-book.id

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

SELECT
  e-book.id,
  CASE
    WHEN e-book.id > 2 THEN 1
    WHEN NOT (e-book.id > 2) THEN 0
  END big_id
FROM e-book
ORDER BY e-book.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 handbook wrapping of subject(Situation) is not obligatory, and you may simply challenge the Situation immediately:

Outcome<Record2<Integer, Boolean>> consequence =
ctx.choose(BOOK.ID, BOOK.ID.gt(2).as("big_id"))
   //               ^^^^^^^^^^^^^ no extra wrapping obligatory
   .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 help BOOLEAN worth expressions. This implies you can too use Situation in different clauses that take Discipline sorts, together with, e.g.:

• GROUP BY or PARTITION BY
• ORDER BY

Time to improve your jOOQ model!