RE: READP equivalent in SQL. -- RPG400-L

I'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.

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/09/2008 03:07:51 PM:

Michael - I think we still have the problem Alan brought up - when
he does a WHERE that uses a greter-than-or-equal, you can't move
relative to anything before the compare value. Is that right?
Because such a record would fail the selection criteria, I think.

-------------- Original message --------------
From: Michael_Schutte@xxxxxxxxxxxx

this is poor example.

D IDX S 6 0

First declare your statement. Since this is an interactive program, you

can just declare as the first thing in your program.
Exec sql
DECLARE C1 Scroll CURSOR FOR
Select * from myfile where conditions match;

// put this in your page up routine only.

// Set cursor back 10 records, if you have 10 records per subfile page.

// I came up with -21 because (-1*10)-11, if 15 records, it'll be 31.

(-1

*15)-16
IDX = -21;
Exec SQL FETCH RELATIVE :IDX FROM C1 INTO :DS

When you load the first subfile, obviously Page Up should be

disabled...

but when you press page down, the cursor will be at position 20 in the
record set. So if you tell it to move back 21 places, you'll be back to

the first record of the previous subfile page.

Now this is assuming that you are leaving the cursor open between page
loads.

Michael Schutte
Admin Professional
ob 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/09/2008 01:23:37 PM:

too much work there's a simpler way to do it without multiple

cursors.

Use a scroll cursor and fetch relative.

Fetch relative allows you to fetch a record set 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.

I don't see that working with key positioning. As you can see in the
example, I am positioning to a given invoice number. As far as SQL is

concerned that there is no invoice less than the value entered so you

cannot do a fetch relative backwards because nothing exists before

the

invoice I positioned to.

If I am missing something, yes, I would appreciate some samples.

--
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.

--
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.

--
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.