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

I think I understand what your issue is about:
You need to position the cursor in such a way to be at your initial key and read all following records without eliminating key2 and key3 values further down the stream
You cannot do:
Select * from my file where key1 >= inputkey1 and key2 >= inputkey2 and key3 >= inputKey3
As this could filter out records you need.
I suggest this as your cursor:

Select * from myfile
Where Key1 concat Key2 Concat key3 >= :inputkey1 concat :inputkey2 concat :inputkey3
Order by key1, key2, key3;

(Or something similar)

Hope that is helpful.

Paul


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Therrien, Paul via MIDRANGE-L
Sent: Wednesday, October 4, 2023 7:43 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Therrien, Paul <ptherrien@xxxxxxxxxxx>; Carel <coteijgeler@xxxxxxxxx>
Subject: RE: Reading the next record in SQL: is there a secret when using OFFSET?

Exsr StartCursor
DoU FKey03
Exsr fetchCursor
[Display you screen and wait for user to hit whatever key you need] Enddo

SR StartCursor
Declare mycursor cursor for
select * from thefile order by key1, key2, key3
Open Cursor
Endsr

Sr FetchCursor
Fetch Next nycursor
Into :whatever
Endsr

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of x y
Sent: Tuesday, October 3, 2023 8:23 PM
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.

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