RE: OPNQRYF record count

["Simon Coulter" <shc@xxxxxxxxxxxxxxxxx>]

Hello Randy,

While your suggestion is a useful technique when opening a single PF or a LF it 
is not reliable when using 
OPNQRYF (particularly if selection criteria are involved), nor is it accurate 
when the file being opened is a 
join or a DYNSLT logical.  This is because there is no guarantee that DB knows 
the number of records being 
returned until it has actually read the records.  OPNQRYF does much of its 
selection as the HLL program 
requests records.

Joins show the number of records in the primary file, DYNSLT show the number of 
records to be processed (not 
returned).  In all cases the number of records value in the INFDS reflects only 
the value when the file was 
opened (static database).

Note that the INFDS does sometimes show the number of records selected by 
OPNQRYF but the point is that it 
cannot be relied upon to do so.  It depends greatly on the way the OPNQRYF is 
specified.

You may want to consider using %COUNT to return the number of records in the 
result set (or one of the 
suggestions based on CPYFRMQRYF.

Regards,
Simon Coulter.

«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»
«» FlyByNight Software         AS/400 Technical Specialists       «»
«» Eclipse the competition - run your business on an IBM AS/400.  «»
«»                                                                «»
«» Phone: +61 3 9419 0175      Mobile: +61 0411 091 400           «»
«» Fax:   +61 3 9419 0175      mailto: shc@flybynight.com.au      «»
«»                                                                «»
«» Windoze should not be open at Warp speed.                      «»
«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»
//--- forwarded letter -------------------------------------------------------
> X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
> MIME-Version: 1.0
> Date: Wed, 10 Mar 99 11:09:51 -0800
> From: "Randy Robb" <rwrobb@pbsilink.com>
> To: MIDRANGE-L@midrange.com
> Reply-To: MIDRANGE-L@midrange.com
> Subject: RE: OPNQRYF record count
> Importance: Normal

> 
> Instead of doing DASD intensive functions like RCVF or CPYFRMQRYF, why not
> use RPG's File Information Data Structure INFDS to retrieve the Current
> number of records?
> In fact, you could write a small program to do this generically.
> 
> Define a file called Fxxx in F-specs, User Controlled open, with the INFDS
> data structure specification in the continuation specs.
> In I-specs (for non-ILE RPG), describe the INFDS, filling the from/to
> positions of the current records field (map of INFDS can be found in RPG
> manual)
> In C-specs, receive the three parms (you can make allowance for MBR being
> blank to use *FIRST), then perform a CL command via QCMDEXC call to override
> file Fxxx to the file named in passed parms.  Then, open the file, retrieve
> the "Current Number of records" from INFDS.
>   Well, you could even use a fourth parameter to return this number to the
> CLP (best passed as alpha and converted to decimal in the CL) to let the CLP
> know number of records found WITHOUT  reading them for a count.

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---