Re: SQLERRD(2) not accurate for SQL with CTE and HAVING clause

Hello,

This code:

EXEC SQL GET DIAGNOSTICS :NumRows = DB2_NUMBER_ROWS;

gives the same result.

Thanks

On Wed, Mar 5, 2025 at 7:48 AM Javier Sanchez <
javiersanchezbarquero@xxxxxxxxx> wrote:

I have not exercised a replication of this. What if you use:

EXEC SQL OPEN TESTCSR;
exec sql get diagnostics :NumRows = DB2_NUMBER_ROWS;

In theory, it should also give the exact number of rows with an insensitive
cursor.
Maybe you can try that out and see if it gives you another value.

HTH

JS

El mié, 5 mar 2025 a las 0:17, Daniel Gross (<daniel@xxxxxxxx>) escribió:

Hi Birgitta,

yes that's true - but I'm also puzzled, because the docs say clearly:

For an OPEN statement, if the cursor is insensitive to changes,

SQLERRD(2) contains the actual number of rows in the result set.


->

https://www.ibm.com/docs/en/i/7.5?topic=area-field-descriptions#rbafzfielddescsqlca__newxxk

And the cursor that Gabe uses is explicitly INSENSITIVE.

Might a defect.

Regards,
Daniel

Am 05.03.2025 um 05:27 schrieb Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx

:

IIRC it contains can also contain an estimated number of rows ... if

the first block of data does not include all rows

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

Daniel Gross

Sent: Tuesday, 4 March 2025 22:00
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Re: SQLERRD(2) not accurate for SQL with CTE and HAVING clause

Hi Gabe,

the problem looks somehow strange.

I was able to replicate the behavior - but not only with "WITH" but

also

without "WITH".

HTH
Daniel

Am 04.03.2025 um 14:57 schrieb Gabe Nelson via RPG400-L <

rpg400-l@xxxxxxxxxxxxxxxxxx>:

Hello,

After I open an insensitive cursor, according to IBM documentation:
"SQLERRD(2) contains the actual number of rows in the result set."
However, this does not seem to be working for my SQL which has a CTE
and a HAVING clause. I simplified my example to make it as generic as

possible:

**FREE

DCL-S NumRows INT(10);

EXEC SQL DECLARE TESTCSR INSENSITIVE CURSOR FOR
WITH IDs (ID) AS (
SELECT Identity FROM TABLET
)
SELECT ID
FROM IDs
GROUP BY ID
HAVING COUNT(*) > 1;

EXEC SQL OPEN TESTCSR;

NumRows = SQLERRD(2);

EXEC SQL CLOSE TESTCSR;

*INLR = *ON;
RETURN;

Assuming "Identity" is unique in TABLET, this SQL should always have 0
results and I would expect NumRows to get set to 0. However, it seems
to be getting set to the number of records in TABLET instead.

Is this a bug or is this an intended case where SQLERRD(2) is an
estimate instead of a real number of rows?

Thank you

--


*Gabe Nelson (he/him)*Software Engineer - Beverage - EDI
gabe.nelson@xxxxxxxxxx www.vtinfo.com <https://public.vtinfo.com/>

--
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or company to whom they

are addressed.

Do not disclose, distribute, or copy this email to others outside your
company. If you have received this email in error, please notify the
sender immediately and delete this email from your system.
--
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.

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

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

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

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