RE: READP equivalent in SQL.

["Alan Campin" <Alan.Campin@xxxxxxxxxxxxxxxx>]

<snip>
'm talking about fetch relative to the record set. Moving the pointer
within the results of the original SQL statement.

The way I understood it, Alan was creating two different SQL statements,
one to handle the page up and another for the page down. If that's the
only reason then you can use the fetch relative instead of using the two
different sql statements.
</snip>

Just to be clear. 

On a green screen, there is a position to field. The operator keys in a
value, say an invoice number. When they press enter, in the old world
you would issue a SETLL to position to record and then roll forward
writing records to the subfile for the number of records on a page. If
they pressed the roll forward again, you would read forward loading
another page of records. 

If the operator press the Rollback key on the first page, you would just
READP until you filled up the page.

Now the problem arises that SQL has nothing that is equivalent to a
SETLL. You cannot position to a record and roll back and forth. 

When you issue the SELECT statement, the database is sub-selected and it
is just as if the records before the WHERE clause don't exist. 

I cannot fetch relative backwards because there is nothing to fetch
backwards to. That is why I doing the two cursors. I need one cursor to
greater than or equal and another to less than. One to get the records
going forward and another getting the records going back. 

I cannot seem to get around that. 

I will agree if I were rolling forward and then wanted to roll back I
could use the Fetch Relative but if I am using the subfile correctly, I
don't need to do that. I can roll back and forth through the records
without ever leaving the subfile. 

I will only come out of the subfile when I try to roll to a page
previous to the first page and I am going to have the Roll back key
pressed on.  

That is when I think I need that second cursor. 

I would be interested if someone has an alternative. Something I am
missing?