RE: Count records in SQL

GET DIAGNOSTICS ... will only return information AFTER an SQL Statement is
run.

ROW_COUNT: (Except from the SQL Reference)
Identifies the number of rows associated with the previous SQL statement
that was executed. If
the previous SQL statement is a DELETE, INSERT, REFRESH, or UPDATE
statement, ROW_COUNT
identifies the number of rows deleted, inserted, or updated by that
statement, excluding rows
affected by either triggers or referential integrity constraints. If the
previous SQL statement is a
MERGE statement, ROW_COUNT identifies the total number of rows deleted,
inserted, and updated
by that statement, excluding rows affected by either triggers or referential
integrity constraints. If the
previous SQL statement is a multiple-row-fetch, ROW_COUNT identifies the
number of rows fetched.
Otherwise, the value zero is returned.

May be DB2_NUMBER_ROWS would be the better option: (Except from the SQL
Reference)
If the previous SQL statement was an OPEN or a FETCH which caused the size
of the result table to
be known, returns the number of rows in the result table. For SENSITIVE
cursors, this value can be
thought of as an approximation since rows inserted and deleted will affect
the next retrieval of this
value. Otherwise, the value zero is returned.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jimmy
Sansi
Sent: Thursday, 12 February 2026 21:44
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Count records in SQL

What about ...

exec sql GET DIAGNOSTICS :Rows = ROW_COUNT;

https://www.ibm.com/docs/en/i/7.5.0?topic=statements-get-diagnostics

On 2026-02-12 12:15, Jerry Forss wrote:

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.