Hi
A “case when” clause in order by JPQL is discarded upon generating SQL, example below.
query=select e from busy$Product e order by (case when e.active = TRUE then 1 when e.active is null then 2 else 3 end), e.updateTs desc, max=10000
is translated to
SELECT ID AS a1, DELETE_TS AS a2, DELETED_BY AS a3, INTERNAL_CODE AS a4, LABEL AS a5, VERSION AS a6 FROM BUSY_PRODUCT WHERE (DELETE_TS IS NULL) ORDER BY UPDATE_TS DESC LIMIT ? OFFSET ?
bind => [10000, 0]
EDIT: more precision on my use case.
User edits a new order, and has to choose a product from a lookup field. There are more than 10000 products to choose from, and I want to present “active” products first, then products for which active status is unknown (active == null), then products not active for sure.
Best Regards
Michael