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;


-- 
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.