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

Would you want to do this with CHAINs in RPG, no!
Why you try to do it then with SELECT ... INTO

Generate a Cursor (DECLARE CURSOR), Open The Cursor (OPEN CURSOR), Loop
through all returned rows with FETCH NEXT FROM CURSIR and finally Close The
Cursor (CLOSE CURSOR)
... this is exactly the same as what you do in RPG (F-Specs / Open+SetLL /
ReadE / Close)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert
Einstein)

-----Original Message-----
From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of x y
Sent: Tuesday, 3 October 2023 11:18
To: Midrange Systems Technical Discussion<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Reading the next record in SQL: is there a secret when using
OFFSET?

I want to do the equivalent of a READE one read at a time.

On a table with a single key, it's easy to read the next record:
SELECT rq1number
INTO :rq1number
FROM grp010
WHERE rq1number > *:rq1number*
ORDER BY rq1number
FETCH FIRST ROW ONLY;

Suppose the table has multiple keys. In all versions of RPG, I can
SETLL(key1:key2) and then READE (key1) to read the next record in the "key1"
group of an indexed file. One application: reading postal codes by
state/province. Reading backward is equally easy. But because SQL doesn't
maintain the equivalent of an open data path (AFAIK), it has to reset its
starting point each time.

Testing OFFSET: this test ran as expected:
SELECT *
FROM arp001
WHERE cmcust >= '0000001'
ORDER BY cmcust --the primary key
OFFSET *1* ROW FETCH FIRST ROW ONLY;

It returns customer 2 as expected.

With a table having more complex keys (11, in this table), things get
complicated. It appears I have to do something like this:

SELECT *
FROM gpp071
WHERE gpjco = *1*
AND gpjdiv = *1*
AND gpjscac = 'DRSA'

*----------------------------* AND gpjtid >= 'HOU'
AND gpjfrom >= '770'
ORDER BY gpjco, gpjdiv, gpjscac, gpjtid, gpjfrom
OFFSET *1* ROWS FETCH FIRST ROW ONLY;

"This" meaning the fixed keys (key1, above the line) are equals but the
variable keys through which I'm scrolling (key2, below the line) need to be
.GE. *and* the variable keys must be in the keylist. For this table, I'll
have to set the eight variable keys as .GE. With each good read, I'll have
the key values I need to push to SQL so it knows where to start after each
"next" request.

OFFSET appears to mean "skip {x} *selected* records.

This will...
SELECT *
FROM arp001
WHERE cmcust = '0000001'
ORDER BY cmcust
OFFSET *1* ROW FETCH FIRST ROW ONLY;
...will fail because the select will find one record--there's no second
record to read.

SQL makes everything else so easy I shouldn't be grexing about one unusual
requirement (but my customers love it). Is there a secret to using OFFSET,
am I approaching the problem all wrong (other options: add a sequence
number, load an array with all the key combinations and read the next key
from element *n + 1)*, or do I just need to beat the code into submission?

Thanks everybody...
--
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 contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.