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

-- 
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: RPG400-L-request-+hD5IHI5Xscn3HwCXmMcX9BPR1lH4CV8@public.gmane.org
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support-FMtJrHiV//lnDLsaKlm4mFaTQe2KTcn/@public.gmane.org for any subscription related questions.