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

[Daniel Gross <daniel@xxxxxxxx>]

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.