Please help converting this SQL to JPQL, problem with subquery (EclipseLink v2)

I have days trying to solve this.

I need to have an ORDER BY and a LIMIT in a subquery, this is the native SQL:

SELECT
    ope.idOperacion AS OperationID,
    est.nombre AS LastStatusName
FROM
    SSCM_4.Operaciones4 ope
INNER JOIN SSCM_4.BitacoraOperaciones4 bita
ON
    bita.operacion = ope.idOperacion
INNER JOIN SSCM_4.Estatus4 est
ON
    est.idEstatus = bita.estatus
WHERE
    ope.idOperacion = 54
AND bita.idBitacoraOperacion =
    (
        SELECT
            BO2.idBitacoraOperacion
        FROM
            SSCM_4.BitacoraOperaciones4 BO2
        INNER JOIN SSCM_4.Estatus4 EST2
        ON
            (
                BO2.Estatus = EST2.idEstatus
            )
        WHERE
            BO2.Operacion = ope.idOperacion
        ORDER BY
            EST2.Prioridad DESC,
            BO2.FechaOperacion DESC,
            BO2.idBitacoraOperacion ASC LIMIT 1
    )

But the problem is that ORDER BY is no supported in subqueries by JPQL, either LIMIT.

The business logic is this: the subquery must return the last status of the operation defined mainly by the priority and date in the rows of the operations journal history that is why I need and ORDER BY and LIMIT clauses.

I need this in JPQL because I have developed an API for dynamic queries, it is a开发者_开发百科 query builder, for JPQL that I need to use.

Thank you in advance.

JPQL does not support this. You need to either use a native SQL query, or rephrase the query, or use multiple queries.

You could potentially change the subquery to use a max instead of the orderby.

You could log a bug/enhancement to have orderby support added to sub-selects, but limit is not standard SQL and not part of JPQL as it is database specific.