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