RE: Help with RPGLE SQL FETCH NEXT

<< 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 ';