RE: READP equivalent in SQL.

Ok, I think I cracked it.

Open up or as needed, two cursors.

The first:

Exec Sql Declare C1 Scroll Cursor For
Select INVOICE,
AMOUNT
From CAPTURE
Where INVOICE >= :InvoiceNumber
Order By 1;

The second:

Exec Sql Declare C2 Scroll Cursor For
Select INVOICE,
AMOUNT
From CAPTURE
Where INVOICE < :InvoiceNumber
Order By 1;

On the first I just do a:

For x = 1 To 3;
Exec Sql Fetch Next
From C1
Into :dsRecord;
EndFor;

For the second, I first issue a:

Exec Sql Fetch Last
From C2
Into :dsRecord;

Then do a loop with prior.

For x = 1 To 3;
Exec Sql Fetch Prior
From C2
Into :dsRecord;
EndFor;

Have tested and seems to work and SQL would more efficient if I fetch into a user space because I can read all records for a page on a single call. Just loop through and write to subfile.

Thanks to those that responded.


<Previous>
I was playing with using SQL in a green screen instead of using file I/O but stumped with this problem. I looked in the archives but don't find anything to for this problem exactly.
 
Normally you have a set of function.
 
PositionInputFile()
LoadOnePage()
LoadPreviousPage()
 
Etc that handle the loading of a subfile.
 
Position input file issues a SETLL and positions the input file based on a input value.
 
LoadOnePage does a READ or READE and reads forward for the number of records on a page and writes records to the subfile.
 
If Roll back key is pressed, the code issues a LoadPreviousPage which loops backwards through the file using READP to position the file and then issues a LoadOnePage to load a page.
 
LoadPreviousPage() is going to have to deal with things like 10 records per page and only 5 records in the file, etc.
 
The problem doing something like this in SQL.
 
I can issues a:
 
Select Field1, Field2 From File1 Where PositionField >= aValue
 
This works fine for reading forward but what happens when you rollback. SQL only sees the records that are greater than or equal to the position to value. If I try to roll back before the value I am going to get a record not found because as far as SQL is concerned the only records in the file are those equal to or greater than the value.
 
How does one do the equivalent of the SETLL, READP, etc?
</Previous>