I found a bug in the JPQL/SQL conversion. Admittedly it is a complex query but a similar format of nested CASE expressions in order by has worked for me elsewhere, I believe.
select e
from chordinate_Ownership e
left join e.owner owningUser
left join e.piece pie
where
owningUser.login = :userId
order by CASE
WHEN e.unattachedPiece = true
THEN
(
CASE
WHEN lower( substring( e.unattachedPieceTitle , 1 , 4 ) ) = 'the '
THEN substring( e.unattachedPieceTitle, 5, length( e.unattachedPieceTitle ) - 4 )
WHEN lower( substring( e.unattachedPieceTitle, 1, 2 ) ) = 'a '
THEN substring( e.unattachedPieceTitle, 3, length( e.unattachedPieceTitle ) - 2)
WHEN lower( substring( e.unattachedPieceTitle, 1, 3 ) ) = 'an '
THEN substring( e.unattachedPieceTitle, 4, length( e.unattachedPieceTitle ) - 3)
ELSE e.unattachedPieceTitle
END
)
ELSE
(
CASE
WHEN lower(substring(pie.title, 1, 4)) = 'the '
THEN substring(pie.title, 5, length(pie.title) - 4)
WHEN lower(substring(pie.title, 1, 2)) = 'a '
THEN substring(pie.title, 3, length(pie.title) - 2)
WHEN lower(substring(pie.title, 1, 3)) = 'an '
THEN substring(pie.title, 4, length(pie.title) - 3)
ELSE pie.title
END
)
END'
2021-06-03 23:24:41.466 ERROR [http-nio-8080-exec-36/legata-core/admin] com.haulmont.cuba.core.sys.ServiceInterceptor - Exception:
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.10-cuba): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: syntax error at or near "ELSE"
Position: 3452
Error Code: 0
Call: SELECT t1.ID AS a1, t1.DELETE_TS AS a2, t1.DELETED_BY AS a3, t1.LIBRARY_ID AS a4, t1.NOTES AS a5, t1.UNATTACHED_PIECE AS a6, t1.UNATTACHED_PIECE_TITLE AS a7, t1.UNAVAILABLE AS a8, t1.USER_DEFINED_COLUMN_VALUES AS a9, t1.USER_GRADE AS a10, t1.VERSION AS a11, t1.OWNER_ID AS a12, t1.PIECE_ID AS a13, t1.PUBLISHED_EDITION_ID AS a14, t0.ID AS a15, t0.DTYPE AS a16, t0.DELETE_TS AS a17, t0.DELETED_BY AS a18, t0.LOGIN AS a19, t0.NAME AS a20, t0.VERSION AS a21, t2.ID AS a22, t2.DELETE_TS AS a23, t2.DELETED_BY AS a24, t2.DURATION AS a25, t2.TITLE AS a26, t2.VERSION AS a27, t2.YEAR_PUBLISHED AS a28, t2.ARRANGER_ID AS a29, t2.ARRANGER2_ID AS a30, t2.CATEGORY_ID AS a31, t2.COMPOSER_ID AS a32, t2.COMPOSER2_ID AS a33, t2.COMPOSER3_ID AS a34, t2.COMPOSER4_ID AS a35, t2.COMPOSER5_ID AS a36, t2.SUB_CATEGORY_ID AS a37, t3.ID AS a38, t3.DELETE_TS AS a39, t3.DELETED_BY AS a40, t3.FIRST_NAMES AS a41, t3.LAST_NAME AS a42, t3.VERSION AS a43, t4.ID AS a44, t4.DELETE_TS AS a45, t4.DELETED_BY AS a46, t4.FIRST_NAMES AS a47, t4.LAST_NAME AS a48, t4.VERSION AS a49, t5.ID AS a50, t5.DELETE_TS AS a51, t5.DELETED_BY AS a52, t5.NAME AS a53, t5.VERSION AS a54, t6.ID AS a55, t6.DELETE_TS AS a56, t6.DELETED_BY AS a57, t6.FIRST_NAMES AS a58, t6.LAST_NAME AS a59, t6.VERSION AS a60, t7.ID AS a61, t7.DELETE_TS AS a62, t7.DELETED_BY AS a63, t7.FIRST_NAMES AS a64, t7.LAST_NAME AS a65, t7.VERSION AS a66, t8.ID AS a67, t8.DELETE_TS AS a68, t8.DELETED_BY AS a69, t8.FIRST_NAMES AS a70, t8.LAST_NAME AS a71, t8.VERSION AS a72, t9.ID AS a73, t9.DELETE_TS AS a74, t9.DELETED_BY AS a75, t9.FIRST_NAMES AS a76, t9.LAST_NAME AS a77, t9.VERSION AS a78, t10.ID AS a79, t10.DELETE_TS AS a80, t10.DELETED_BY AS a81, t10.FIRST_NAMES AS a82, t10.LAST_NAME AS a83, t10.VERSION AS a84, t11.ID AS a85, t11.DELETE_TS AS a86, t11.DELETED_BY AS a87, t11.NAME AS a88, t11.VERSION AS a89, t12.ID AS a90, t12.DELETE_TS AS a91, t12.DELETED_BY AS a92, t12.VERSION AS a93, t12.COLLECTION_ID AS a94, t12.PUBLISHER_ID AS a95, t13.ID AS a96, t13.DELETE_TS AS a97, t13.DELETED_BY AS a98, t13.TITLE AS a99, t13.VERSION AS a100, t13.PUBLISHER_ID AS a101, t14.ID AS a102, t14.DELETE_TS AS a103, t14.DELETED_BY AS a104, t14.NAME AS a105, t14.VERSION AS a106, t15.ID AS a107, t15.DELETE_TS AS a108, t15.DELETED_BY AS a109, t15.NAME AS a110, t15.VERSION AS a111 FROM CHORDINATE_OWNERSHIP t1 LEFT OUTER JOIN SEC_USER t0 ON (t0.ID = t1.OWNER_ID) LEFT OUTER JOIN CHORDINATE_PIECE t2 ON (t2.ID = t1.PIECE_ID) LEFT OUTER JOIN CHORDINATE_COMPOSER t3 ON (t3.ID = t2.ARRANGER_ID) LEFT OUTER JOIN CHORDINATE_COMPOSER t4 ON (t4.ID = t2.ARRANGER2_ID) LEFT OUTER JOIN CHORDINATE_PIECE_CATEGORY t5 ON (t5.ID = t2.CATEGORY_ID) LEFT OUTER JOIN CHORDINATE_COMPOSER t6 ON (t6.ID = t2.COMPOSER_ID) LEFT OUTER JOIN CHORDINATE_COMPOSER t7 ON (t7.ID = t2.COMPOSER2_ID) LEFT OUTER JOIN CHORDINATE_COMPOSER t8 ON (t8.ID = t2.COMPOSER3_ID) LEFT OUTER JOIN CHORDINATE_COMPOSER t9 ON (t9.ID = t2.COMPOSER4_ID) LEFT OUTER JOIN CHORDINATE_COMPOSER t10 ON (t10.ID = t2.COMPOSER5_ID) LEFT OUTER JOIN CHORDINATE_PIECE_SUB_CATEGORY t11 ON (t11.ID = t2.SUB_CATEGORY_ID) LEFT OUTER JOIN CHORDINATE_PUBLISHED_EDITION t12 ON (t12.ID = t1.PUBLISHED_EDITION_ID) LEFT OUTER JOIN CHORDINATE_COLLECTION t13 ON (t13.ID = t12.COLLECTION_ID) LEFT OUTER JOIN CHORDINATE_PUBLISHER t14 ON (t14.ID = t13.PUBLISHER_ID) LEFT OUTER JOIN CHORDINATE_PUBLISHER t15 ON (t15.ID = t12.PUBLISHER_ID) WHERE ((t0.LOGIN = ?) AND (t1.DELETE_TS IS NULL)) ORDER BY CASE ELSE CASE WHEN (t1.UNATTACHED_PIECE = ?) THEN CASE WHEN (LOWER(SUBSTR(t1.UNATTACHED_PIECE_TITLE, ?, ?)) = ?) THEN SUBSTR(t1.UNATTACHED_PIECE_TITLE, ?, (LENGTH(t1.UNATTACHED_PIECE_TITLE) - ?)) WHEN (LOWER(SUBSTR(t1.UNATTACHED_PIECE_TITLE, ?, ?)) = ?) THEN SUBSTR(t1.UNATTACHED_PIECE_TITLE, ?, (LENGTH(t1.UNATTACHED_PIECE_TITLE) - ?)) WHEN (LOWER(SUBSTR(t1.UNATTACHED_PIECE_TITLE, ?, ?)) = ?) THEN SUBSTR(t1.UNATTACHED_PIECE_TITLE, ?, (LENGTH(t1.UNATTACHED_PIECE_TITLE) - ?)) ELSE t1.UNATTACHED_PIECE_TITLE END WHEN (LOWER(SUBSTR(t2.TITLE, ?, ?)) = ?) THEN SUBSTR(t2.TITLE, ?, (LENGTH(t2.TITLE) - ?)) WHEN (LOWER(SUBSTR(t2.TITLE, ?, ?)) = ?) THEN SUBSTR(t2.TITLE, ?, (LENGTH(t2.TITLE) - ?)) WHEN (LOWER(SUBSTR(t2.TITLE, ?, ?)) = ?) THEN SUBSTR(t2.TITLE, ?, (LENGTH(t2.TITLE) - ?)) ELSE t2.TITLE END END LIMIT ? OFFSET ?
for context, an OWNERSHIP object can relate to a PIECE object where we can read the title information or we can record the title information directly form the OWNERSHIP. I did find a work-around for my use case requiring only one CASE expression in the order-by clause:
select e from chordinate_Ownership e left join e.owner owningUser left join e.piece pie where owningUser.login = :userId and ( coalesce(pie.title, e.unattachedPieceTitle) like '%st%' ) order by
CASE
WHEN lower(substring(coalesce(e.unattachedPieceTitle,pie.title), 1, 4)) = 'the '
THEN substring(coalesce(e.unattachedPieceTitle,pie.title), 5, length(coalesce(e.unattachedPieceTitle,pie.title))-4)
WHEN lower(substring(coalesce(e.unattachedPieceTitle,pie.title), 1, 2)) = 'a '
THEN substring(coalesce(e.unattachedPieceTitle,pie.title), 3, length(coalesce(e.unattachedPieceTitle,pie.title))-2)
WHEN lower(substring(coalesce(e.unattachedPieceTitle,pie.title), 1, 3)) = 'an '
THEN substring(coalesce(e.unattachedPieceTitle,pie.title), 4, length(coalesce(e.unattachedPieceTitle,pie.title))-3)
ELSE coalesce(e.unattachedPieceTitle,pie.title)
END
So I am not in dire need of a fix, I just thought I’d let you know.