× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.