Re: SQL/CLI Count of rows returned in result set

On 29-May-2015 20:47 -0500, Scott Klement wrote:

I'm very interested in this discussion, as we tried to find a way to
do this a few years ago, and the best we could find was to do
something like:

SELECT COUNT(*) FROM (the-statement)

Correct. Getting the actual row COUNT is the consistently accurate
means. Another option is to output the result set and get the record count
from the member of the file that was created for that purpose. If that
/output/ is in the form of a derived-table [implemented as a true
temporary] there is a mildly reduced impact to performance more noticeable
for small result sets. The following Common Table Expression (CTE) for
example, used to obtain the count in the result-set for the SELECT:

with
ts as (the-select-statement)
select
(select count(*) from ts) as sc
, ts.*
from ts

We tried the various diagnostic fields, and all either gave an
"estimated" count, or didn't return anything at all until you had
already read the result set.

The Open indeed may be able to give only an /estimated/ row count; until
a[n actual or effective] FETCH of every selected row is complete, the
actual number is unknown:


<
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzfielddescsqlca.htm

IBM i 7.2->Database->Reference->SQL reference->SQLCA (SQL communication
area)->
_Field descriptions_
SQLERRD(2) ...
...
For an OPEN statement, if the cursor is insensitive to
changes, SQLERRD(2) contains the actual number of rows in the result set.
If the cursor is sensitive to changes, SQLERRD(2) contains an estimated
number of rows in the result set.
..."

Our goal was to create a scrollbar for a grid that we were loading
one page at a time from SQL. The count is needed to determine the
total size so that we can make the scrollbar show the position
accurately. But nothing we've tried has worked aside from doing a
COUNT() of the statement as a subselect, or reading the whole result
set twice (which isn't performant enough)

Using insensitive cursors, mostly from temporary\sorted results, is the
best to avoid the estimates.

--
Regards, Chuck


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.