Re: Count records in SQL

Il 12/02/2026 21:15, Jerry Forss ha scritto:

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?

There are several different ways to solve the problem you pose, basically a "select count(*)" run first with the same selection and grouping criteria, even better if you replace the "*" with a key field, otherwise you can use something like:

Exec sql get diagnostics :rows = DB2_NUMBER_ROWS;

or even in the SQLCA you can find the reference to the number of extracted records.
Just be careful that defining the cursor as sensitive or insensitive could change the calculation of the extracted rows.
Not only that, afaicr to use the SQLCA you need to have fetched at least one record while get diagnostics also works simply by opening the cursor.
I see you are preparing a dynamic statement in these cases I do this:

Sqlstring='your statement goes into here';
Exec Sql prepare S1 from :Sqlstring;
Exec Sql declare C1 insensitive cursor with hold for S1;
Exec Sql Open C1;
if Sqlcode < 0;
//something went wrong log it and see
elseIf sqlcode = 0:
Exec sql get diagnostics :rrows = DB2_NUMBER_ROWS;
endif;