Search code examples
jpajpa-2.0jpqlnamed-query

JPA @NamedQuery correlated subquery

I'm trying to create a @NamedQuery that will contain the following correlated subquery:

@NamedQuery(name = "Drcomments.findByOrderDesc", query = " SELECT a FROM ( SELECT ROWNUMBER() OVER(ORDER BY d.drcommentsPK.commentSecondaryCode DESC) AS EL_ROWNM , d.drcommentsPK.commentSecondaryCode , d.commentSecondaryCodeDescription FROM Drcomments d WHERE d.drcommentsPK.commentPrimaryCode = 1 ) a WHERE a.EL_ROWNM < 4 and a.EL_ROWNM > 0 ")})

The problem is that I get this error:

"Exception [EclipseLink-8025] (Eclipse Persistence Services - 2.3.0.v20110604-r9504):  org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [Drcomments.findByOrderDesc:  SELECT a FROM ( SELECT ROWNUMBER() OVER(ORDER BY d.drcommentsPK.commentSecondaryCode DESC) AS EL_ROWNM , d.drcommentsPK.commentSecondaryCode , d.commentSecondaryCodeDescription FROM Drcomments d WHERE d.drcommentsPK.commentPrimaryCode = 1 ) a WHERE a.EL_ROWNM < 4 and a.EL_ROWNM > 0 ], line 1, column 15: unexpected token [(].
Internal Exception: org.eclipse.persistence.internal.jpa.parsing.jpql.InvalidIdentifierException"

Does @NamedQuery supports correlated subquerys ? If so, How can I create a named query that will perform this kind of query ?

Thank's In Advance.

Solution

  • @NamedQuery does not support SQL, it supports only JPQL. Your query is clearly not JQPL, because it uses database specific syntax like ROWNUMBER(). Additionally in JPQL subqueries are supported only in WHERE and HAVING clauses.

    Use @NamedNativeQuery instead.