Case When syntax expression in jooq

I am trying to reproduce this MySQL query in jooq

select case     
when year(d.date) - p.birth_year < 5 then '<5' 
else '5+'
end as age
from partners p join departure d on d.id = p.id

to

this.schema().select(
DSL.decode().value(dateDiff(p.BIRTHDATE , date(d.DATE)))
   .when(greaterThan(5), "above 5")
   .when(lessThan(5), "under 5")
   .otherwise("unknown").as("age"),
   .from(p)
   .join(d).on(d.ID.eq(p.ID))

Answer

What you seem to be looking for is the SQL:2003 simple CASE expression, which allows to form something like “partial predicates” based on the case value, akin to pattern matching in other languages. This is not yet supported in jOOQ, see Issue #3223.

Interestingly, though, your SQL example doesn’t use this syntax, nor does it correspond to your suggested jOOQ API usage. I suspect you wanted to use this syntax to avoid repeating the subtraction twice. This could be done also as follows, in SQL:

select 
  case sign(year(d.date) - p.birth_year - 5)
  when -1 then '<5'
  when  0 then '5+'
  when  1 then '5+'
          else 'unknown' end AS age
from partners p join departure d on d.id = p.id

This would translate to:

Partners p = PARTNERS.as("p");
Departure d = DEPARTURE.as("d");

using(configuration)
   .select(choose(sign(year(d.DATE).minus(p.BIRTH_YEAR).minus(5)))
       .when(inline(-1), val("<5"))
       .when(inline( 0), val("5+"))
       .when(inline( 1), val("5+"))
       .otherwise(inline("unknown"))
       .as("age"))
   .from(p)
   .join(d).on(d.ID.eq(p.ID))
   .fetch();

This static import is implied:

import static org.jooq.impl.DSL.*;