JPQL for select from select row_number() over()

Question

I'm using Db2 on AS/400, and I am trying to execute a JPQL query that will return results from row x to row y. In SQL this works: ``` select cur.* from ( SELECT ROW_NUMBER() OVER() AS ROWNUM FROM tableName d) as cur WHERE cur.ROWNUM > 0 AND cur.ROWNUM < 10 ``` How can I do this in JQPL? I tried it in many ways but every time I got an exception. I want to limit my result inside the query, and not by using the setMaxResult, setFirstResult methods.

Accepted Answer

That cannot be done. JPQL operates to entities and entities are mapped to tables in database. Row number in db2 is concept in result set, not in database table.

JPQL for select from select row_number() over()

Solution

Problem