Re: Master file vs. Work file

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

How does the selection differ? What is the processing you need to do in the
middle?

Is the master file the only data source? Then I suggest grouping results by
product ID. Create a format file (empty physical file) with just the
product ID and a sales amount field (perhaps a little larger than the one
in the master file, in order to handle sums). Name that file as the format
file on the OPNQRYF command. Name product ID in the GRPFLD parameter. Map
the sales amount total field from the format file to %SUM(sales). Make that
mapped field a KEYFLD, descending. The result is the top selling products
by sales amount. (Some details may be a little off - haven't used this
beast for years now.)

Boy, it's been awhile since I used OPNQRYF. This is SO much easier with
SQL. Even if you don't have the SQL dev Kit/QM product installed, you can
use Query Management queries. In this case,

select PRODID, sum(SLSAMT) TOTSLS from MSTRFILE group by SLSAMT order by
totsls desc

is the statement you put into a source physical file, rcdlen(91). Then
CRTQMQRY (prompt it - you'll see what to do). Then STRQMQRY with
OUTPUT(*OUTFILE) OUTFILE(LIB/FILE). Your RPG works with that outfile,
which, of course, must exist at compile time. That outfile is similar to
the output of CPYFRMQRYF.

Vern

At 10:14 AM 9/2/02 +0000, you wrote:
> Hello everybody,
>
> I have to create a report of most popular products based on sales amount
> in a given period.
>
> Case 1
> 1.      Read an OPNQRYF based on a master file (@ 100,000 records in
> master file)
> 2.      Write work file based on the selection (@ 1000 records)
> 3.      Read work file with key1 (@ 1000 records)
> 4.      Do some processing
> 5.      Read work file with key 2 (@ 1000 records)
> 6.      Print report
>
> Case 2
> 1.      Read an OPNQRYF based on a huge master file
> 2.      Write work file based on the selection (@ 10 records, yes only 10
> records)
> 3.      Read work file with key 1 (@ 10 records)
> 4.      Do some processing
> 5.      Read OPNQRYF based on the same huge master file (Will it read only
> 1000 records selected by OPNQRYF or read again the whole file?)
> 6.      Print report
>
> Which is the better option? Reading master file twice OR writing work file
> with more records (10 vs. 1000) and read it?
>
> TIA,
> Deepak