retrieving data from the table in chunks for paging

I have a table that has huge amounts of data and I want to retrieve the data from the table in blocks through an application. I don't want to load all the data into memory and then create pages from there. Instead I want to get the data in blocks from the table and display them at each page and when the user goes to next page I can get the next block. So first page should execute something like: select * from invoice FOR ROWS FROM 1 -100 and then the second page from 101 - 200 and so on. Is there any row number or any way that I can count the rows that I am retrieving in IDS 9.40? Thank you

On Mar 27, 3:57 pm, Gentian Hila <genti.t...@gmail.com> wrote: > I have a table that has huge amounts of data and I want to retrieve > the data from the table in blocks through an application. > > I don't want to load all the data into memory and then create pages > from there. Instead I want to get the data in blocks from the table > and display them at each page and when the user goes to next page I > can get the next block. > > So first page should execute something like: > > select * from invoice> > FOR ROWS FROM 1 -100 > > and then the second page from 101 - 200 and so on. > > Is there any row number or any way that I can count the rows that I am > retrieving in IDS 9.40? > > Thank you You don't have skip in 9.4 but you do have first. We used to do it in the following way: select first 100 * from invoice where <<primary-key>> > <<last- primary-key-retrieved>> order by <<primary key>>; make sure that <<primary key>> is indexed and that the query is using the index for the query. You don't have to use the <<primary key>> but you need to make sure that you have an index on the field or fields, and you have to do an order by In your application you will grab the last primary key to put it into sql above as you display the last record. Initialize it to something less than the minimum primary key at the beginning.