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

What exactly is your Problem?

Does the following not work?

Select ... into :YourHostvariables
From YourTable ...
Order By ...
Limit 1 Offset x;

Again the question: Why do you try to replace a READE (in a Loop) with a CHAIN? You won't do it in pure RPG so why to try it with (embedded) SQL

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: Wednesday, 4 October 2023 02:23
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Carel <coteijgeler@xxxxxxxxx>
Subject: 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.

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