Toplink Exception, whats wrong?

Question

Hey,

I've got an excpetion when I generate this EJB SQL Statement.

    Exception Description: Syntax error parsing the query [SELECT h FROM Busmodul h WHERE LOWER(h.modulNummer) LIKE :modulnummer AND h.einbauort.id = :einbauort_fk AND h.plattform.id = :plattform_fk ORDER BY TRIM(TRAILING '-' FROM CONCAT('0', h.modulNummer))], line 1, column 150: syntax error at [TRIM].
Internal Exception: line 1:150: expecting IDENT, found 'TRIM'

Whats the meaning of IDENT. Any Ideas what I am doing wrong?

Regards

Answer 1

I think the first thing to note is that EJB-QL is not the same thing as SQL even though it looks similar, so it does not always behave in the same way as one would expect a SQL query to behave.

Although TRIM is specified in the EJB-QL spec, ORDER BY must use something returned by the SELECT statement. In this case, the result of the TRIM function is not an identifier that can satisfy the ORDER BY clause. From the JPA Query Language Syntax section of the Java EE 5 Tutorial:

When using the ORDER BY clause, the SELECT clause must return an orderable set of objects or values. You cannot order the values or objects for values or objects not returned by the SELECT clause.

You will find more information on the Java Persistance Query Language in the relevant chapter of the Java EE 5 Tutorial.

Answer 2

Your ORDER BY clause is not valid, you can't order on the values or objects for values or objects not returned by the SELECT clause (see chapter 4.9 ORDER BY Clause of the EJB 3.0 JPA specification).

BTW, I couldn't find any mention of the use of SQL functions and aggregate functions in the ORDER BY clause in the Query Language chapter of the EJB 3.0 specification so I don't know if this is supported.