OPNQRYF can help you adapt reports to the needs of many users.
by Ted Holt
It's very common around my shop for a user to call the data processing department and say something along the lines of, "I really like the report Joe Smith gets. I'd like to get the same report, except that I only need to see figures for department 4, and I'd like to have it sorted by date instead of customer number." Chances are, that sounds familiar to you. If you read my article in last month's issue, you should not be surprised when I say that such requests are often easily handled by the OPNQRYF command. The techniques we'll discuss this month combined with what we talked about last month will help you write applications which are flexible and easily adapted to the needs of different users.
More About Record Selection
We learned last month that a high-level language (HLL) program which accesses data through the open data path (ODP) created by OPNQRYF, does not need to and should not select records to be processed. Instead, OPNQRYF should be given that task, since it can select the records much more quickly. Record selection is coded in a free-format string in the QRYSLT parameter. The HLL program is coded as if it will process the entire file.
In the following paragraphs we'll look at some functions and an operator which increase the power of and simplify coding of the QRYSLT parameter. These examples will require the use of four database files: CUSMAST (customer master), SLSHIST (sales history), ITMMAST (item master), and REPMAST (sales rep master). These files are described in 1.
In the following paragraphs we'll look at some functions and an operator which increase the power of and simplify coding of the QRYSLT parameter. These examples will require the use of four database files: CUSMAST (customer master), SLSHIST (sales history), ITMMAST (item master), and REPMAST (sales rep master). These files are described in Figure 1.
Because it is very common (in our imaginary sales system) for information from the sales history and the customer master to be combined based on a common customer number, we will define a join logical file over these two files. This will give us a format to reference in our HLL programs, and will eliminate the need to code join criteria in OPNQRYF commands.
It is possible (though not likely) that an erroneous customer number may find its way into the sales history file, so let's make SLSHIST the primary file and add the JDFTVAL keyword to the DDS. JDFTVAL tells OS/400 that any SLSHIST record which has no corresponding CUSMAST record will be joined to a dummy CUSMAST record made up of default values (usually blanks for character fields and zeros for numeric ones). This type of join is called a left-outer-join.
2 shows the DDS for the join file, which we have named CUSHIST. We have given it a key so we'll be able to code the file as a keyed file in HLL programs which will use the file. The actual key field(s) will be specified at run-time in the KEYFLD parameter of OPNQRYF. (I will cover more detail on join files in a follow-up article next month. In the meantime, you can refer to the Data Base Guide, SC21-9620.)
Figure 2 shows the DDS for the join file, which we have named CUSHIST. We have given it a key so we'll be able to code the file as a keyed file in HLL programs which will use the file. The actual key field(s) will be specified at run-time in the KEYFLD parameter of OPNQRYF. (I will cover more detail on join files in a follow-up article next month. In the meantime, you can refer to the Data Base Guide, SC21-9620.)
If we wish to list sales for one day, we can use the *EQ operator (e.g., QRYSLT('SLSDAT *EQ 900401')) to select only that day's sales. For a monthly report, we could code QRYSLT('SLSDAT *GE 900401 *AND SLSDAT *LE 900430'), but the %RANGE function is simpler. CL program SLSC1A, shown in 3, demonstrates the use of this function. If parameter &MONTH_YEAR is passed the value '0490', the QRYSLT parameter re-solves to 'SLSDAT *EQ %RANGE(900400 900499)', selecting only April 1990 sales. Other CL programs could build weekly, quarterly, or yearly reports in the same manner.
If we wish to list sales for one day, we can use the *EQ operator (e.g., QRYSLT('SLSDAT *EQ 900401')) to select only that day's sales. For a monthly report, we could code QRYSLT('SLSDAT *GE 900401 *AND SLSDAT *LE 900430'), but the %RANGE function is simpler. CL program SLSC1A, shown in Figure 3, demonstrates the use of this function. If parameter &MONTH_YEAR is passed the value '0490', the QRYSLT parameter re-solves to 'SLSDAT *EQ %RANGE(900400 900499)', selecting only April 1990 sales. Other CL programs could build weekly, quarterly, or yearly reports in the same manner.
Since there is no way to predict at compilation time which sales records will be selected at run time, a 40-byte character parameter (&SUBTITLE) is used to pass a report title to the CALLed report program SLSR1.
Another powerful function is %VALUES, which takes the place of multiple *OR's. To produce a report of our largest-selling items, we could code QRYSLT('ITMNBR *EQ 30002 *OR ITMNBR *EQ 30014 *OR ITMNBR *EQ 30017'), but that's rather cumbersome. The %VALUES function takes one or more arguments, and any record containing any of the specified values will be selected. Take a look at the QRYSLT parameter of program SLSC1B shown in 4 for an illustration.
Another powerful function is %VALUES, which takes the place of multiple *OR's. To produce a report of our largest-selling items, we could code QRYSLT('ITMNBR *EQ 30002 *OR ITMNBR *EQ 30014 *OR ITMNBR *EQ 30017'), but that's rather cumbersome. The %VALUES function takes one or more arguments, and any record containing any of the specified values will be selected. Take a look at the QRYSLT parameter of program SLSC1B shown in Figure 4 for an illustration.
You can improve string searches by using the *CT (contains) operator. Unlike *EQ, which selects just the records with exactly matching values, *CT allows to you match records on a substring of a field. Program SLSC2 in 5 demonstrates the power of *CT. The CL statement CALL SLSC2 'WIDGET' would generate QRYSLT('ITMDES *CT "WIDGET"'), and would retrieve records whose descriptions were '1/2 INCH WIDGET', 'LEFT-HANDED WIDGET- PULLER', and 'WIDGET KIT'.
You can improve string searches by using the *CT (contains) operator. Unlike *EQ, which selects just the records with exactly matching values, *CT allows to you match records on a substring of a field. Program SLSC2 in Figure 5 demonstrates the power of *CT. The CL statement CALL SLSC2 'WIDGET' would generate QRYSLT('ITMDES *CT "WIDGET"'), and would retrieve records whose descriptions were '1/2 INCH WIDGET', 'LEFT-HANDED WIDGET- PULLER', and 'WIDGET KIT'.
The *CT operator, like the relational operators *EQ or *GT, is case- sensitive. If we wish to ignore cases, we have the %XLATE function, which will translate from one character set to another. We could use table QSYSTRNTBL to translate both the search string and the target field to upper case before the comparison is made. 6 shows the modifed QRYSLT parameter.
The *CT operator, like the relational operators *EQ or *GT, is case- sensitive. If we wish to ignore cases, we have the %XLATE function, which will translate from one character set to another. We could use table QSYSTRNTBL to translate both the search string and the target field to upper case before the comparison is made. Figure 6 shows the modifed QRYSLT parameter.
By the way, the QRYSLT parameter accepts both variable and expressions. Using a variable is almost always better, because you can build more complex selection criteria,and most importantly, you can see the resultant QRYSLT expression in a program dump if your OPNQRYF command terminates abnormally.
So far we've considered record selection based on one file. One nice thing about join files, however, is that we can select records based on criteria from more than one file. Program SLSC5 in 7 joins four files, SLSHIST, CUSMAST, ITMMAST, and REPMAST, with an inner join, and selects joined records for processing if they fall within a certain period of time (determined by data from SLSHIST) and pertain to a certain sales representative (determined by data from CUSMAST). We have added some flexibility by ignoring the sales rep selection when parameter &REP_NBR is zero. This allows us to print a report for a single sales rep or for all sales reps.
So far we've considered record selection based on one file. One nice thing about join files, however, is that we can select records based on criteria from more than one file. Program SLSC5 in Figure 7 joins four files, SLSHIST, CUSMAST, ITMMAST, and REPMAST, with an inner join, and selects joined records for processing if they fall within a certain period of time (determined by data from SLSHIST) and pertain to a certain sales representative (determined by data from CUSMAST). We have added some flexibility by ignoring the sales rep selection when parameter &REP_NBR is zero. This allows us to print a report for a single sales rep or for all sales reps.
We can also select records which have no match in another file. Program SLSC6 (8) prints a report showing customers who have no sales in the sales history file. The JDFTVAL parameter has a value of *ONLYDFT, which means that only primary file records with no matching secondary records will be selected. The HLL program which it calls is a simple "read-a- record print-a-line" report program in which CUSMAST is the input primary file. It can be called by other CL programs to print customer master listings of all types (e.g., all customers, or customers for a certain sales representative).
We can also select records which have no match in another file. Program SLSC6 (Figure 8) prints a report showing customers who have no sales in the sales history file. The JDFTVAL parameter has a value of *ONLYDFT, which means that only primary file records with no matching secondary records will be selected. The HLL program which it calls is a simple "read-a- record print-a-line" report program in which CUSMAST is the input primary file. It can be called by other CL programs to print customer master listings of all types (e.g., all customers, or customers for a certain sales representative).
Grouping Functions
The techniques discussed above work with one record at a time. Grouping functions, on the other hand, are designed to return results gathered from a group of records, which may be the entire file or only the records which share a common field value.
Two very simple functions are %COUNT, which returns the number of records in a group, and %SUM, which returns the sum of the values in a numeric field. Let's look at program SLSC3A in 9 to get an idea how to use them.
Two very simple functions are %COUNT, which returns the number of records in a group, and %SUM, which returns the sum of the values in a numeric field. Let's look at program SLSC3A in Figure 9 to get an idea how to use them.
The GRPFLD (group field) parameter tells OPNQRYF by which fields to summarize. In this case, records will be summarized by customer number. The MAPFLD (map field) parameter tells how the fields are to be calculated. CUSSCT will contain the number of records found for each customer. CUSSLS will accumulate the extended sales for a customer. OPNQRYF will send to the HLL program one record for each distinct customer number in the SLSHIST file.
Since no physical file contains these summary figures, we must create a dummy shell file, CUSSUM, to define the customer number, count, and sum fields to HLL program SLSR3, shown in 10. All such dummy files can be compiled with the MBR(*none) option, because they contain no data. Specifying the CUSSUM file in the FORMAT parameter of the OPNQRYF command will cause OPNQRYF to pass the data to the HLL program in the correct format.
Since no physical file contains these summary figures, we must create a dummy shell file, CUSSUM, to define the customer number, count, and sum fields to HLL program SLSR3, shown in Figure 10. All such dummy files can be compiled with the MBR(*none) option, because they contain no data. Specifying the CUSSUM file in the FORMAT parameter of the OPNQRYF command will cause OPNQRYF to pass the data to the HLL program in the correct format.
The QRYSLT parameter may still be used in summary queries. Any records not matching the QRYSLT criteria will not be included in the summary totals.
If we wish to omit certain summary records on the basis of summary figures, we can do so with the GRPSLT (group select) parameter. Program SLSC3B in 11 includes the parameter GRPSLT('CUSSLS *GE 2000'). OPNQRYF will now omit any customers who did not buy at least $2,000 of merchandise.
If we wish to omit certain summary records on the basis of summary figures, we can do so with the GRPSLT (group select) parameter. Program SLSC3B in Figure 11 includes the parameter GRPSLT('CUSSLS *GE 2000'). OPNQRYF will now omit any customers who did not buy at least $2,000 of merchandise.
The CL program in 11 calls a program that produces a summary report, printing one line for each customer summary record it reads. However, the summary functions do not have to be used only in summary reports.
The CL program in Figure 11 calls a program that produces a summary report, printing one line for each customer summary record it reads. However, the summary functions do not have to be used only in summary reports.
The CL program in 12 and the program it calls produce a detailed sales report with features not available to HLL programs. Since no GRPFLD parameter is specified, only one record, containing the average sale amount, the highest sale, the lowest sale, and the sum of all sales, will be sent to the report program (not listed in this article), which will retrieve it on the first cycle. Each detail record in the file can be compared to these figures, so that the report can flag the highest sale, lowest sale, and above-average sales, as well as percentages of the total sales.
The CL program in Figure 12 and the program it calls produce a detailed sales report with features not available to HLL programs. Since no GRPFLD parameter is specified, only one record, containing the average sale amount, the highest sale, the lowest sale, and the sum of all sales, will be sent to the report program (not listed in this article), which will retrieve it on the first cycle. Each detail record in the file can be compared to these figures, so that the report can flag the highest sale, lowest sale, and above-average sales, as well as percentages of the total sales.
I don't know how things are in your shop, but in my shop we don't have time to write a dozen reports which show basically the same information with slight variations. The detail selection and group selection capabilities, combined with the dynamic sorting ability of OPNQRYF, are so powerful that I can often satisfy everyone's wishes with a generic RPG program called by one or more CL programs which use OPNQRYF.
You may come across some pleasant surprises while using these techniques. For instance, I took the record selection logic out of an RPG program and put it into an OPNQRYF command. Run time went from 2 hours and 15 minutes to 35 seconds. I can't guarantee that you'll get results like that, but you'll never know until you try, will you?
Flexible Reporting with Open Query File
Figure 1 DDS specifications for sample files
Figure 1: Customer Master, Sales History, Item Master, and Sales Representative Master DDS Specifications A* CUSTOMER MASTER FILE CUSMAST A A UNIQUE A R CUSMASTR A CUSNBR 4 TEXT('CUSTOMER NUMBER') A CUSNAM 15 TEXT('CUSTOMER NAME') A CUSADD 25 TEXT('ADDRESS') A CUSCTY 15 TEXT('CITY') A CUSSTT 2 TEXT('STATE') A CUSZIP 10 TEXT('CUSTOMER ZIP') A CUSCLS 1 0 TEXT('CUSTOMER CLASS') A REPNBR R REFFLD(REPNBR REPMAST) A K CUSNBR A* SALES HISTORY FILE A* A R SLSHISTR A ORDNBR 6S 0 TEXT('ORDER NUMBER') A CUSNBR R REFFLD(CUSNBR CUSMAST) A SLSDAT 6S 0 TEXT('DATE OF SALE') A ITMNBR R REFFLD(ITMNBR ITMMAST) A QTY 7 0 TEXT('QUANTITY') A UPRICE 9 2 TEXT('UNIT PRICE') A* ITEM MASTER FILE A UNIQUE A R ITMMASTR A ITMNBR 5 0 TEXT('ITEM NUMBER') A ITMDES 30 TEXT('ITEM DESCRIPTION') A K ITMNBR A* SALES REPRESENTATIVE MASTER FILE REPMAST A A UNIQUE A R REPMASTR A REPNBR 3 0 TEXT('SALES REP NUMBER') A REPNAM 15 TEXT('SALES REP NAME') A REPCOM 3 3 TEXT('COMMISSION RATE') A K REPNBR
Flexible Reporting with Open Query File
Figure 10 File format for sales summary report
Figure 10: File format for sales summary report A* SHELL FILE FOR SALES SUMMARY BY CUSTOMER A* MAY BE COMPILED MBR(*NONE) A R CUSSUMR A CUSNBR R REFFLD(CUSNBR CUSMAST) A CUSSLS 11 2 TEXT('TOTAL AMT SOLD TO CUST') A CUSSCT 5 0 TEXT('NUMBER OF SALES TO CUST') A K CUSNBR
Flexible Reporting with Open Query File
Figure 11 CL program SLSC3B - summary sales, major customers
Figure 11: SLSC3B Summary Sales Report of Major Customers CL Program PGM PARM(&MONTH_YEAR) DCL &MONTH_YEAR *CHAR 4 DCL &YEAR_MONTH *CHAR 4 DCL &YEAR *CHAR 2 DCL &MONTH *CHAR 2 DCL &QRYSLT *CHAR 256 DCL &SUBTITLE *CHAR 40 CHGVAR &MONTH %SST(&MONTH_YEAR 1 2) CHGVAR &YEAR %SST(&MONTH_YEAR 3 2) CHGVAR &YEAR_MONTH (&YEAR *CAT &MONTH) CHGVAR &QRYSLT ('SLSDAT *EQ %RANGE(' *CAT + &YEAR_MONTH *CAT '00' *BCAT + &YEAR_MONTH *CAT '99)') CHGVAR VAR(&SUBTITLE) VALUE('OF MAJOR CUSTOMERS FOR + MONTH OF' *BCAT &MONTH *CAT '/' *CAT &YEAR) OVRDBF FILE(CUSSUM) TOFILE(SLSHIST) SHARE(*YES) OPNQRYF FILE((SLSHIST)) + FORMAT(CUSSUM) + QRYSLT(&QRYSLT) + KEYFLD((CUSSLS *DESCEND)) + GRPFLD(CUSNBR) + GRPSLT('CUSSLS *GE 2000') + MAPFLD((EXTPRICE 'QTY * UPRICE') + (CUSSCT '%COUNT') + (CUSSLS '%SUM(EXTPRICE)')) CALL PGM(SLSR3) PARM(&SUBTITLE) CLOF OPNID(SLSHIST) DLTOVR FILE(CUSSUM) ENDPGM
Flexible Reporting with Open Query File
Figure 12 CL program SLSC4 - sales report with details
Figure 12: SLSC4 Sales Report with Detail Figures CL Program PGM DCL VAR(&PARM) TYPE(*CHAR) LEN(40) VALUE(' -- + ALL SALES') OVRDBF FILE(SUMFIGS) TOFILE(SLSHIST) SHARE(*YES) OPNQRYF FILE((SLSHIST)) FORMAT(SUMFIGS) + MAPFLD((EXTPRICE 'QTY * UPRICE' *DEC 11 2) + (TOTSAL '%SUM(EXTPRICE)') + (AVGSAL '%AVG(EXTPRICE)') + (MINSAL '%MIN(EXTPRICE)') + (MAXSAL '%MAX(EXTPRICE)')) CALL PGM(SLSR4) PARM(&PARM) CLOF OPNID(SLSHIST) DLTOVR FILE(SUMFIGS) ENDPGM
Flexible Reporting with Open Query File
Figure 2 The Join logical file
Figure 2: The Join Logical File A* JOIN SALES HISTORY FILE & CUSTOMER MASTER A A JDFTVAL A R CUSHIST JFILE(SLSHIST CUSMAST) A J JOIN(SLSHIST CUSMAST) A JFLD(CUSNBR CUSNBR) A CUSNBR JREF(1) A CUSNAM A CUSADD A CUSSTT A CUSZIP A CUSCLS A ORDNBR A SLSDAT A ITMNBR A QTY A UPRICE A K ORDNBR
Flexible Reporting with Open Query File
Figure 3 CL program SLSC1A - Monthly Sales Report
Figure 3: SLSC1A Monthly Sales Report CL Program PGM PARM(&MONTH_YEAR) DCL &MONTH_YEAR *CHAR 4 DCL &YEAR_MONTH *CHAR 4 DCL &MONTH *CHAR 2 DCL &YEAR *CHAR 2 DCL &QRYSLT *CHAR 256 DCL &SUBTITLE *CHAR 40 CHGVAR &MONTH %SST(&MONTH_YEAR 1 2) CHGVAR &YEAR %SST(&MONTH_YEAR 3 2) CHGVAR &YEAR_MONTH (&YEAR *CAT &MONTH) CHGVAR &QRYSLT ('SLSDAT *EQ %RANGE(' *CAT + &YEAR_MONTH *CAT '00' *BCAT + &YEAR_MONTH *CAT '99)') CHGVAR &SUBTITLE ('FOR MONTH OF' *BCAT &MONTH + *CAT '/' *CAT &YEAR) OVRDBF FILE(CUSHIST) SHARE(*YES) OPNQRYF FILE((CUSHIST)) + QRYSLT(&QRYSLT) + KEYFLD((ORDNBR)) CALL PGM(SLSR1) PARM(&SUBTITLE) CLOF OPNID(CUSHIST) DLTOVR FILE(CUSHIST) ENDPGM
Flexible Reporting with Open Query File
Figure 4 CL program SLSC1B - Largest selling items
Figure 4: SLSC1B Largest Selling Items Report CL Program PGM DCL &SUBTITLE *CHAR 40 CHGVAR &SUBTITLE ('FOR LARGEST-SELLING ITEMS') OVRDBF FILE(CUSHIST) SHARE(*YES) OPNQRYF FILE((CUSHIST)) + QRYSLT('ITMNBR *EQ + %VALUES(30002 30014 30017)') + KEYFLD((ITMNBR)) CALL PGM(SLSR1) PARM(&SUBTITLE) CLOF OPNID(CUSHIST) DLTOVR FILE(CUSHIST) ENDPGM
Flexible Reporting with Open Query File
Figure 5 CL program SLSC2 - search on item description
Figure 5: SLSC2 Search on Item Description CL Program PGM PARM(&SEARCHSTRG) DCL VAR(&SEARCHSTRG) TYPE(*CHAR) LEN(40) OVRDBF FILE(ITMMAST) SHARE(*YES) OPNQRYF FILE((ITMMAST)) + QRYSLT('ITMDES *CT "' *CAT + &SEARCHSTRG *TCAT '"') + KEYFLD(ITMNBR) CALL PGM(SLSR2) PARM(&SEARCHSTRG) CLOF OPNID(ITMMAST) DLTOVR FILE(ITMMAST) ENDPGM
Flexible Reporting with Open Query File
Figure 6 OPNQRY modification
Figure 6: OPNQRYF modification OPNQRYF FILE((ITMMAST)) QRYSLT('%XLATE(ITMDES + QSYSTRNTBL) *CT %XLATE("' *CAT &SEARCHSTRG + *TCAT '" QSYSTRNTBL)') KEYFLD((ITMNBR))
Flexible Reporting with Open Query File
Figure 7 CL program SLSC5 - Monthly Sales Report
Figure 7: SLSC5 Monthly Sales Report CL Program PGM PARM(&REP_NBR &MONTH_YEAR) DCL &REP_NBR *CHAR 3 DCL &REP_NBR_D *DEC (3 0) DCL &MONTH_YEAR *CHAR 4 DCL &YEAR_MONTH *CHAR 4 DCL &MONTH *CHAR 2 DCL &YEAR *CHAR DCL &QRYSLT *CHAR 256 DCL &SUBTITLE *CHAR 40 CHGVAR &MONTH %SST(&MONTH_YEAR 1 2) CHGVAR &YEAR %SST(&MONTH_YEAR 3 2) CHGVAR &YEAR_MONTH (&YEAR *CAT &MONTH) CHGVAR &REP_NBR_D (&REP_NBR) CHGVAR &QRYSLT ('SLSDAT *EQ %RANGE(' *CAT + &YEAR_MONTH *CAT '00' *BCAT + &YEAR_MONTH *CAT '99)') IF (&REP_NBR_D *NE 0) DO CHGVAR VAR(&QRYSLT) + VALUE(&QRYSLT *BCAT '*AND REPNBR + *EQ' *BCAT &REP_NBR) ENDDO CHGVAR &SUBTITLE ('FOR MONTH OF' *BCAT &MONTH + *CAT '/' *CAT &YEAR) IF (&REP_NBR_D *EQ 0) DO CHGVAR VAR(&SUBTITLE) + VALUE(&SUBTITLE *BCAT '- ALL REPS') ENDDO ELSE DO CHGVAR VAR(&SUBTITLE) + VALUE(&SUBTITLE *BCAT '- REP' *BCAT + &REP_NBR *BCAT 'ONLY') ENDDO OVRDBF FILE(REPHIST) TOFILE(SLSHIST) SHARE(*YES) OPNQRYF FILE((SLSHIST) (CUSMAST) (ITMMAST) (REPMAST)) + FORMAT(REPHIST) QRYSLT(&QRYSLT) + KEYFLD((REPNBR) (ORDNBR)) + JFLD((SLSHIST/ITMNBR ITMMAST/ITMNBR) + (SLSHIST/CUSNBR CUSMAST/CUSNBR) + (CUSMAST/REPNBR REPMAST/REPNBR)) + MAPFLD((CUSNBR 'SLSHIST/CUSNBR') + (REPNBR 'CUSMAST/REPNBR') + (ITMNBR 'SLSHIST/ITMNBR')) CALL PGM(SLSR5) PARM(&SUBTITLE) CLOF OPNID(SLSHIST) DLTOVR FILE(REPHIST) ENDPGM
Flexible Reporting with Open Query File
Figure 8 CL program SLSC6 - list inactive customers
Figure 8: SLSC6 List Inactive Customers CL Program PGM DCL &SUBTITLE *CHAR 40 CHGVAR VAR(&SUBTITLE) VALUE('- INACTIVE CUSTOMERS') OVRDBF FILE(CUSMAST) SHARE(*YES) OPNQRYF FILE((CUSMAST) (SLSHIST)) + FORMAT(CUSMAST) + KEYFLD((CUSNBR)) + JFLD((CUSMAST/CUSNBR SLSHIST/CUSNBR)) + JDFTVAL(*ONLYDFT) + MAPFLD((CUSNBR 'CUSMAST/CUSNBR')) CALL PGM(SLSR6) PARM(&SUBTITLE) CLOF OPNID(CUSMAST) DLTOVR FILE(CUSMAST) ENDPGM
Flexible Reporting with Open Query File
Figure 9 CL program SLSC3A - summary sales report by custom
Figure 9: SLSC3A Summary Sales Report by Customer CL Program PGM PARM(&MONTH_YEAR) DCL &MONTH_YEAR *CHAR 4 DCL &YEAR_MONTH *CHAR 4 DCL &YEAR *CHAR 2 DCL &MONTH *CHAR 2 DCL &QRYSLT *CHAR 256 DCL &SUBTITLE *CHAR 40 CHGVAR &MONTH %SST(&MONTH_YEAR 1 2) CHGVAR &YEAR %SST(&MONTH_YEAR 3 2) CHGVAR &YEAR_MONTH (&YEAR *CAT &MONTH) CHGVAR &QRYSLT ('SLSDAT *EQ %RANGE(' *CAT + &YEAR_MONTH *CAT '00' *BCAT + &YEAR_MONTH *CAT '99)') CHGVAR &SUBTITLE ('FOR MONTH OF' *BCAT &MONTH + *CAT '/' *CAT &YEAR) OVRDBF FILE(CUSSUM) TOFILE(SLSHIST) SHARE(*YES) OPNQRYF FILE((SLSHIST)) + FORMAT(CUSSUM) + QRYSLT(&QRYSLT) + KEYFLD((CUSNBR)) + GRPFLD(CUSNBR) + MAPFLD((EXTPRICE 'QTY * UPRICE') + (CUSSCT '%COUNT') + (CUSSLS '%SUM(EXTPRICE)')) CALL PGM(SLSR3) PARM(&SUBTITLE) CLOF OPNID(SLSHIST) DLTOVR FILE(CUSSUM) ENDPGM
Ted Holt is IT manager of Manufacturing Systems Development for Day-Brite Capri Omega, a manufacturer of lighting fixtures in Tupelo, Mississippi. He has worked in the information processing industry since 1981 and is the author or co-author of seven books.
MC Press books written by Ted Holt available now on the MC Press Bookstore.
 |
||
 |
|
Complete CL: Fifth Edition Become a CL guru and fully leverage the abilities of your system. List Price $79.95 Now On Sale
|
|
||
 |
|
Complete CL: Sixth Edition Now fully updated! Get the master guide to Control Language programming. List Price $79.95
Now On Sale
|
|
||
 |
|
IBM i5/iSeries Primer Check out the ultimate resource and “must-have” guide for every professional working with the i5/iSeries. List Price $99.95 Now On Sale
|
|
||
 |
|
Qshell for iSeries Check out this Unix-style shell and utilities command interface for OS/400. List Price $79.95
Now On Sale
|
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online