JPQL order by clause with "case when" ignored

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.

image

Best Regards
Michael

Hi Michael!

Could you please provide more information about your case?
It will help me to investigate if you send the following information:

  1. platform version;
  2. the way how you execute the query (is it query in some dataloader defined in a XML or maybe you use DataManager bean, etc.)
  3. some piece of logs for execution time.

Regards,
Oleg.