Pagination or records per page

[tpass001]

:: Environment DB2 V7.1.1 on Z/OS. ::

I am looking for a sql query that would return rows 20 through 30 of a query.

For example
select * from ( select a.*, rownum rnum from (select job, empno from emp order job asc) a )where rnum>=20 and rnum<30

This is in oracle, but is there a way to do this using db2 v7.1.1 on zos?

Please let me know, I have been looking for this for a while now and have come to believe that there is no solution to this.

Thanks.

TP.

 

[MarcLodge]

TP,
See thread178-946086 for some helpful hints, but ultimately on the version and platform you are talking about, I don't think there is a piece of SQL that you can run to do what you wish.

But if you prove me wrong, PLEASE let me know!!!

Marc

 

[juliane26]

Marc is quite right,

rownum or rowid are not an SQL concept and actually it is sometimes dangerous to use them in business logic (e.g. after a reorg a resultset may change)

anyway,
there is a clause in DB2 'fetch first row' or 'fetch first 20 rows'

the SQL would look like:

>select ..
>from ...
>where ...
>fetch first 10 rows only;

this is no standard SQL anymore but serves great for testing purposes.

The 'fetch-first-clause' is also available on z/OS, and using something like
select *
from t1
where ...
AND x not in (
select *
from t1
where ...
fetch first 20 rows only)
fetch first 10 rows only

might do the job for rows 20 to 30, but I did not try it.

Juliane


Juliane