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

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

<boolean worth expression> ::=
  <predicate>

The precise definition accommodates intermediate guidelines, however you get the concept. 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 in reality, earlier than SQL:1999 standardised on the BOOLEAN information sort, 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 unusual <worth expression>.

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

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 at all times supported a manner to make use of Situation and Area<Boolean> exchangeably. There are the 2 wrapper strategies:

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

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

End result<Record2<Integer, Boolean>> outcome =
ctx.choose(BOOK.ID, subject(BOOK.ID.gt(2)).as("big_id"))
   .from(BOOK)
   .orderBy(BOOK.ID)
   .fetch();

The generated SQL appears 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 characteristic:

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 guide wrapping of subject(Situation) is now not needed, and you may simply challenge the Situation immediately:

End result<Record2<Integer, Boolean>> outcome =
ctx.choose(BOOK.ID, BOOK.ID.gt(2).as("big_id"))
   .from(BOOK)
   .orderBy(BOOK.ID)
   .fetch();

The behaviour is strictly the identical as in the event you had wrapped the situation (together with the outcome sort), and the emulation nonetheless kicks in additionally for Oracle and different dialects that don’t assist BOOLEAN worth expressions. This implies you may also use Situation in different clauses that take Area varieties, together with, e.g.:

• GROUP BY or PARTITION BY
• ORDER BY

Time to improve your jOOQ model!