Re: Help with RPGLE SQL FETCH NEXT

There are several "problems" with that SQL.

c/exec SQL
c+ declare mainCursor Cursor
c+ for mainSelect
c/end-exec

The cursor is not defined INSENSITIVE - without INSENSITIVE the cursor is ASENSITIVE - which means, that the cursor probably can see changes that are made while it's open.

But the main question might be, are PREPARE and OPEN executed every time the web service is "called"? Because if it is so, the INSENSITIVE keyword wouldn't help either, as the cursor is re-opened every time.

As I wrote, it is generally not a good idea, to use OFFSET - it's better to receive the "last seen item", use a WHERE clause that filters out anything "before" that "last seen item" and use LIMIT xx (short for FETCH NEXT xx ROWS) to limit the SQL query even before the FETCH does is.

This way, you are quite safe against OFFSET/LIMIT "window drifting" as described by Markus Winand (https://use-the-index-luke.com/no-offset).

HTH
Daniel

Am 10.02.2026 um 17:00 schrieb gio.cot via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>:



<< Can you send the whole SQL statement and especially the cursor
definition?



Here it is:



c/exec SQL

c+ declare mainCursor Cursor

c+ for mainSelect

c/end-exec



c/exec sql

c+ prepare mainSelect

c+ from :mainStatement

c/end-exec





MAINSTATEMENT = 'SELECT '

+ ' a.aakey, a.aadesx, a.bniitx, a.aaclax, a.aagrux,
a.aalsix, '

+ ' a.aadtcx '

+ ' FROM item00f a '

+ ' left join itemX00F x '

+ ' On a.Aakey = x.ArxAr '

+ ' INNER JOIN ( '

+ ' SELECT g.gmky1, g.gmkey '

+ ' FROM invent00f g '

+ ' WHERE '

+ ' mcmagx IN ( '

+ w_a +'001'+ w_a + ','+w_a+'002'+w_a+','+w_a+ '006' + w_a

+ ' ) '

+ ' and g.mcgdix > 0 '

+ ' GROUP BY g.gmky1, g.gmkey '

+ ' ) g ON a.aaky1 = g.gmky1 '

+ ' and a.aakey = g.Gmkey '

+ ' LEFT JOIN mytbl00f t'

+ ' ON a.aaky1 = t.tuky1 '

+ ' AND ' + w_a + 'ALX' + w_a + ' = t.tusuf '

+ ' AND a.aalsix = t.tuele '

+ ' WHERE '

+ ' a.aastsx = ' + w_a + 'A' + w_a

+ ' and a.aaflax <> ' + w_a + 'A' + w_a ;





MAINSTATEMENT = %trim(Mainstatement)

+ ' Order By a.aadtcx DESC, a.aaclax, a.aagrux, a.Aakey
'

+ ' OFFSET ( ' + %char(PagNum) + ' - 1) * 60 ROWS '

+ ' FETCH NEXT 60 ROWS ONLY '

+ ' with NC '

+ ' FOR READ ONLY ';

--
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@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.