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 <[email protected]>:
> 
> 
> 
> << 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: [email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: [email protected]
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
> 
> Please contact [email protected] for any subscription related questions.
> 
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected] for any subscription related questions.