Using SQL the right way was (Re: Reading the next record in SQL: is there a secret when using OFFSET?)

You can't just replace RLA with SQL.

Sure your users may be used to a POSITION TO and being able to roll
backward. But that isn't the way to use SQL.

With RLA you're limited to one access path at a time, you position yourself
and then move forward or back.

SQL offers so much more flexibility. But you need to retrain your users to
FILTER and SORT rather than position too.

Many years ago, I re-worked an accounting application. Removing the
ability to select a specific access path and POSITION TO.

Instead I added a screen where the users could input various filters
(including wildcards) and then decide in what order they wanted the data.

My oh my, did the users complain....

For about a week...maybe two.

Then they started requesting the same ability on various other
applications. :)

Just my $0.04 (adjusted for inflation )
Charles



On Tue, Oct 3, 2023 at 3:18 AM x y <xy6581@xxxxxxxxx> wrote:

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.