Re: 1st RPG program with embedded SQL

You can't scroll back past the first record...

WHERE momast.plann = :svplann AND
momast.ostat < '55' and
momast.odudt >= :rsvdate

So if Rsvdate is 1190820, then you're not going to be able to go back
before that.

One solution I've seen is to define/open a second cursor with
momast.odudt < Rsvdate

If the user tries to go backward past the start of the first, you open up
the other cursor and read from it.

However, my preferred solution is to not try to emulate "position-to"
provided by the limits of RLA.

Instead, offer the user "filter-on"/"subset-on" and "order by" ...lots more
options available there.

Whereas with RLA, you're position-to was dependent linked to the order. In
SQL, filtering and ordering are two completely separate things.

In 20 years, I've never had a user ask for the "position-to" to come back
after I've shown them the power of "filter-on" + "order by"

SQL shouldn't be a drop in replacement for RLA, you really need to rework
the app to take advantage of SQL.

Charles





On Tue, Aug 20, 2019 at 11:14 AM Jim Hawkins <jhawkins@xxxxxxxxxxxx> wrote:

declare sqlplandate inSENSITIVE scroll cursor for
SELECT momast.ordno,
momast.fitem,
momast.odudt,
momast.orqty,
momast.ostat,
momast.fdesc,
momast.opcur,
momast.qtyrc,
momast.latdt,
momast.plann,
momast.qtdev,
momast.sstdt,
morout.awrkc,
morout.opseq,
morout.opstc,
morout.ltrdt,
morout.tqctd
FROM amfliba.momast
JOIN amfliba.morout ON momast.ordno = morout.ordno
WHERE momast.plann = :svplann AND
momast.ostat < '55' and
momast.odudt >= :rsvdate
order by momast.odudt,
momast.ordno,
morout.opseq;

Svplann is a numeric value (as is plann) that comes from the screen.
Rsvdate is a date in cymmdd format that is a manipulation of an 8 digit
field entered on the screen in yyyymmdd format and verified.

It appears to scroll forward works fine when the new date is entered, the
scroll backward seems to be the issue. If it I reach a date prior to
rsvdate, it appears to position to anywhere and the dates are not in
sequence even when scrolling forward.

exec sql
fetch relative :rrns1 from sqlplandate
into :(List of program field names)

rrns1 = 1 - (rr1 + sflsiz + 1)
rr1 is the current number of records on the subfile page
sflsiz = maximum record in the subfile
so rrns becomes a negative value


Regards,

Jim Hawkins
Programmer Analyst
Interkal LLC
Kalamazoo, MI


--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com