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.

Answer 1

Query q = em.createQuery("select e from SomeEntity e")
            .setFirstResult(0)
            .setMaxResults(10);

Answer 2

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.