Re: GET DIAGNOSTICS question

On Wed, 10 May 2023 at 18:40, Alan Cassidy <cfuture@xxxxxxxxxxx> wrote:

Thanks Buck! I've been using the DIAGNOSTIC values, but that seems a
more sure thing.

Be sure to read Charles' reply - he was much clearer than I regarding
DIAGNOSTIC values.

But wait, you need a SELECT to do the count, and you can't do it when
you open a cursor. I don't think you would run the cursor and then do
this count with the same SELECT statement, so how is this?

When I need a count before starting a processing loop, I run the
SELECT COUNT(*) INTO...first - no cursor.
Yes, this means two separate exec sql blocks.
But I very rarely need to know how many records there are _before_ I
start processing them. (Reeve apparently does, thus my reply).
It's more typical for me to simply process them until I get to the end
of the subfile or end of the result set. If I need a count of those I
FETCHed or UPDATEd, I use SQLER3. That's especially useful if I'm
FETCHing FOR n ROWS.

The SQL cursors I use take a long time. It's probably quicker for the
second SELECT, but I don't think that much.

The optimiser can be a pleasant surprise that way. SELECT COUNT(*)
always seems to run faster than selecting a bunch of columns over a
bunch of rows.
In general, if I have slow performing cursors, I look at creating some
new indexes as a first step.
--buck