The Open Query File (OPNQRYF) command allows you to summarize groups of records by specifying one or more fields in the GRPFLD parameter. Using this technique, you can create one query record for each group of records. Normally, you can only include fields other than the ones you're grouping by in the query record if they are summarized with the %SUM function. However, by using the %MAX function on fields that are not going to be grouped or summarized (e.g., description and date fields), you can include all of the fields in the original record format and actually summarize a database file using its own format.

This method provides a number of benefits:

? Eliminates the usual requirement to create an additional record format.

? Eliminates the need to override to another file.

? Allows the HLL program to view the same file and format.

? Makes the fields not grouped or summed available.

? Provides an alternative to Format Data (FMTDTA) summarizing.

3 and 4 provide an example of this technique. 3 shows the description of the SALESPF file. 4 shows a CL program that uses OPNQRYF to summarize the Sales amount (SALAMT) by the Product number (PRDNO). Note that you must qualify all mapped field names with the file name. That way, you avoid the OPNQRYF error of referencing a field to itself, which in this case is exactly what is being done. Also, be sure that any fields you summarize (in this case SALAMT) are large enough to contain the total value for a group.

Figures 3 and 4 provide an example of this technique. Figure 3 shows the description of the SALESPF file. Figure 4 shows a CL program that uses OPNQRYF to summarize the Sales amount (SALAMT) by the Product number (PRDNO). Note that you must qualify all mapped field names with the file name. That way, you avoid the OPNQRYF error of referencing a field to itself, which in this case is exactly what is being done. Also, be sure that any fields you summarize (in this case SALAMT) are large enough to contain the total value for a group.

? Tom Conover

TechTalk: Taking OPNQRYF to the %MAX

Figure 3: Sales File SALESPF

 A R SALESR TEXT('Sales Record') A PRDNO 12A TEXT('Product Number') A PRDDSC 50A TEXT('Description') A SALAMT 7P 2 TEXT('Sales Amount') A INVYR 4S 0 TEXT('Invoice Year') A INVMM 2S 0 TEXT('Invoice Month') A INVDD 2S 0 TEXT('Invoice Day') 
 
TechTalk: Taking OPNQRYF to the %MAX
 
Figure 4: OPNQRYF Summarizing Technique
 
 PGM OVRDBF FILE(SALESPF) SHARE(*YES) OPNQRYF FILE((SALESPF)) KEYFLD((PRDNO)) + GRPFLD(PRDNO) + MAPFLD((PRDDSC '%MAX(SALESPF/PRDDSC)') + (SALAMT '%SUM(SALESPF/SALAMT)') + (INVYR '%MAX(SALESPF/INVYR)') + (INVMM '%MAX(SALESPF/INVMM)') + (INVDD '%MAX(SALESPF/INVDD)')) CALL PGM(HLLPGM) CLOF OPNID(SALESPF) DLTOVR FILE(SALESPF) ENDPGM