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.

score 12 · Answer 1 · answered Jun 26 '12 at 11:41

12

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

answered Jun 26 '12 at 11:41

JB Nizet

678,734
91
1,224
1,255

8

How can I get the filed ROWNUM ? @JB Nizet – verystrongjoe Sep 11 '15 at 07:28
3

Ugh, this is _explicitly_ not what OP wants. – sleske Aug 28 '19 at 10:23

score 4 · Accepted Answer · answered Jun 26 '12 at 11:54

4

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.

answered Jun 26 '12 at 11:54

Mikko Maunu

41,366
10
132
135

JPQL for select from select row_number() over()

2 Answers2

Linked