RE: READP equivalent in SQL.

too much work there's a simplier way to do it without multiple cursors.
Use a scroll cursor and fetch relative.

Fetch relative allows you to fetch a recordset relative to your current
position.
If you are on 11, you fetch negative 10 and your back to one.

If you need a sample let me know.


Michael Schutte
Admin Professional

Bob Evans Bob-B-Q: You haven't had barbeque until you've had Bob Evans
Bob-B-Q. Try our six Bob-B-Q dishes, starting at $5.99. For more
information, visit www.BobEvans.com.


rpg400-l-bounces@xxxxxxxxxxxx wrote on 06/06/2008 10:12:08 PM:

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>

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.