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

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.