Case when not equal in jooq

user_143269 0
Viewed 15

I have two case statements in an order by.

One sorts FIELDB DESC if FIELDA matches a pre-defined list of values. (Unrelated but since I see no way to replace WHEN IN ("val1", "val2"), I define each value as its own when).

queryBuilder.addOrderBy(case_(MYTABLE.FIELDA)
  .when("val1", MYTABLE.FIELDB)
  .when("val2", MYTABLE.FIELDB)
  .desc());

However, I need to sort everything that doesn't match those values by FIELDB ASC

Problem is, I can't see a way to convert this sql to jooq:

CASE WHEN `mytable`.`fielda` != "val1" THEN `mytable`.`fieldb` END ASC
jooq
1 Answers

The case_(value) API is used to model the standard SQL <simple case>, not the <searched case>. Your expression produces:

CASE fielda WHEN val1 THEN fieldb WHEN val2 THEN fieldb END

It doesn't model what you seemingly intended (the <searched case>)

CASE WHEN fielda = val1 THEN fieldb WHEN fielda = val2 THEN fieldb END

Why not just use the API for the <searched case>, then, instead?

when(MYTABLE.FIELD.ne(val1), MYTABLE.FIELDB)

Both syntaxes are also documented in the manual.

user_5217990
Related