Hi Jerry,
of course, the easiest way to count records is a static SQL like:
exec sql select count(*)
into :nCountVar
from ...
But as I have seen, you have a dynamic SQL to tables in different schemas. And that's not possible in static SQL without creating aliases first.
But with dynamic SQL you can't have an INTO clause in your SELECT statement - so a possible solution would be:
SqlSelect = 'select count(*) ' +
'From ' + %Trim(PurgeXALib) + '/MBC6REP ' +
'Where C6ACDT <= ' + %EditC(PurgeDateCYMD : 'X');
SqlSelect = 'values ('+SqlSelect+') into ?';
exec sql prepare stmSelectCount from :SqlSelect;
exec sql execute stmSelectCount using :nCountVar;
You have to wrap your SELECT in a VALUES/INTO statement with a dynamic parameter marker. Then prepare and execute it.
The solutions using GET DIAGNOSTICS after the OPEN doesn't help, as the database does not always return the exact number of rows in the cursor - it can either be estimated or (as this might be an ASENSITIVE or SENSITIVE cursor) it can change during the fetch loop.
But if you want to read all the records anyway, you can do the following:
SqlSelect = 'SELECT count(*) over(), ' +
'C6DcCd, '+
'C6CvNb, '+
'C6AcDt, ' +
'C6FnSt,' +
'C6B9Cd ' +
'From ' + %Trim(PurgeXALib) + '/MBC6REP ' +
'Where C6ACDT <= ' + %EditC(PurgeDateCYMD : 'X');
exec sql prepare stmSqlSelect from :SqlSelect;
exec sql declare csrSqlSelect INSENSITIVE cursor for stmSqlSelect;
exec sql open...
exec sql fetch csrSqlSelect into :nCountVar, ...;
This way you have to actual number of rows with every fetch in the variable. As the cursor is declared as INSENSITIVE, the rows won't change, even when new rows matching the criteria are inserted during runtime.
COUNT(*) OVER()
is the simpliest form of an OLAP expression (aka window function). It's a very powerful feature of modern SQL and massively underrated and underutilized.
->
https://www.ibm.com/docs/en/i/7.6.0?topic=expressions-olap-specifications
In essence, you can calculate typical aggregates on every level of your select statement without using GROUP BY. Like having a grouped counter and row number over groups of rows or creating running totals, ranks and a lot more.
If you need the row count and the records, I would do it with OLAP. If you only need the count, the VALUES INTO solution is good.
What is the purpose of the program? Or better what doesn't do with the fetched rows?
If you need more help, feel free to contact me via the list or in private.
HTH
Daniel
Am 12.02.2026 um 21:16 schrieb Jerry Forss <JForss@xxxxxxxxxxxxx>:
I have a SQL
SqlSelect = 'SELECT C6DcCd, ' +
'C6CvNb, '+
'C6AcDt, ' +
'C6FnSt,' +
'C6B9Cd ' +
'From ' + %Trim(PurgeXALib) + '/MBC6REP ' +
'Where C6ACDT <= ' + %EditC(PurgeDateCYMD : 'X');
Instead of reading through the cursor, I want the number of records found.
How do I do that?
--
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.
midrange.com
