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

Again: the thing that makes this a complicated problem is the fact that
every "read next" operation has to figure out where is was and read the
next record

I'll start with a select key1='pet' and key2='cat' and key3='purple' and
I'll find my record

Key1 Key2 Key3
pet bird blue
pet bird green
pet bird red
pet cat blue
pet cat green
pet cat purple <---I read this record first.
pet cat red <---This is the next record I want to get. How?
pet dog blue
pet dog green
pet dog green

I want to advance to the next record by pressing a command key. Is there
an elegant way to do this?

Remember, all the SELECT knows is the pet/cat/purple row. Neither FETCH
(which is a basically READE) nor OFFSET (also READE) will retrieve any
other rows.






On Tue, Oct 3, 2023 at 3:39 AM Carel via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

What about FETCH?


Op 3-10-2023 om 11:17 schreef x y:

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 contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.