Re: Reading the next record in SQL: is there a secret when using OFFSET?

Peter, Dieter is right. A cursor will select only a subset of rows/records
from a table/file unless the WHERE clause is not included, which will
select all rows.
You will not be able to fetch the previous record before 'pet' unless you
use the LIKE keyword instead for the equals sign.

Also, you will have to traverse the cursor either from top to bottom (or
back if it's a scrollable cursor) to find one that has a specific key
value. You cannot get to a specific row in the middle of the record set,
you can only jump one by one unless you use FETCH relative k records ahead
if k is positive, or back if k is negative. You can also jump at once to
the bottom of the cursor, even past the last row with the LAST keyword and
to the top of it with FECTH FIRST (or FETCH BEFORE and then issue a normal
FETCH NEXT).

The iACS sql scripts console is a good way to model what you want and you
can then see the behavior of the cursor by scrolling up or down the result
set.
If this is not what you are looking for, the you will have to keep using
RLA.

JS


El vie, 6 oct 2023 a las 1:35, Dsternb (<dieter.bender@xxxxxxxxxxxx>)
escribió:

<Peter>
If the WHERE clause specifies the key > 'pet', are you saying that a FETCH
PREVIOUS will get the record prior to the one with a key of 'pet'?

If not, then you're saying you have to expand the result set to include
record prior to 'pet'. If that's true, then how do you position the SQL
cursor to fetch the 'pet' record after opening the cursor?
</Peter>

... using RLA, you would have to decide at the beginning, if you wanna go
upstairs or downstairs - same in SQL. If you change your decision after
going some steps, you would need a second cursor (it should not be too
hard
to find the solution, having a look to my previous post). This might
happen
in some subfile programms. Leaving the preselected segment, seems alittle
bit strange to me, but I've seen such progtramms?!
The SQL way for this would be to pull the data to the programm in large
blocks (pulling 1000 records is nearly as fast as reading one record) -
after this, you could navigate in these 1000 records in your programm,
coming to the end, you would load the next block.

Going from RLA to SQL is more than translating RLA code to SQL data
access.
Coding RLA, you are walking up or down a data path (mostly one table!) per
setxx and read*p, chainig additional data from othe tables. The SQL way
would be to pull all needed data with one SQL statement, oftly with one
single fetch. Using Views, you could hide the structure of your database
to
the application layer. In your programm, you would see select * from
someView, the where clause sets some conditions (as the user wants), the
order by clause would sort the data, as the user wants. The same programm
could allow very diffrent conditions and sort criteria and you would get
much more flexibility for your users.

D*B


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.