This article expands on the post sort processing introduced in previous KDs and the sample files provided in the install of the product.
Writing Advantage CA-Earl Report Requests: Advanced Post sort Processing Techniques: Samples and Instructions
See KDs - TEC291378, TEC323377, TEC265260, TEC265262. TEC274567
This article expands on the postsort processing introduced in previous KDs and the sample files provided in the install of the product. Using the standard AIRPORTS file, we will produce a report showing the total number of passengers for each airport, the percentage that passenger total represents with respect to all of the airports included in the report and the average number of passengers per airport within each country. This report will be printed in order by city name within country code, and a subtotal number and percent of passengers will be printed for each country.
Instead of printing the country code, we want to print the full country name. We could include a series of IF-THEN-ELSE statements, or a DECODE statement, in the presort section of the report to assign the country name for each input record. However, it is far more efficient to assign the country name with a DECODE(D) in the postsort section of the report. By testing the BREAKQSEQ variable, we will only have to assign the country name once for each country processed.
Here is the final CA-Earl request:
NOTE ======================================================= NOTE This is the OPTIONS section - used to override the NOTE default options specified at install time NOTE ======================================================= OPTION LIST ON OPTION OMIT ALL BLANK LINES OPTION PRINTER=121 NOTE ======================================================= NOTE This is the File Definitions section - the file and NOTE fields to be used are identified here NOTE ======================================================= AIRPORTS: FILE DISK RECORD=80 DEF A_NAME 1-18 X 'NAME OF' 'AIRPORT' DEF A_CITY 20-35 X 'CITY' DEF A_COUNTRY 40-43 X 'COUNTRY' 'CODE' DEF A_PASS 50-57 N 'NUMBER OF' 'PASSENGERS' PIC 'ZZ,ZZZ,ZZ9' NOTE ======================================================= NOTE This is the GSA section - comparable to the COBOL NOTE working-storage section NOTE ======================================================= ! DEF COUNTRY (X 24) = NONE 'Country' 'Name' DEF BRK_PASS (N 12.0) = 0 '# PASSENGERS' 'FOR COUNTRY' PIC 'ZZZ,ZZZ,ZZZ,ZZ9' DEF WK_PCT (N 3.4) = 0 DEF BRK_PCT (N 3.2) = 0 '% OF TOTAL' 'PASSENGERS' PIC 'ZZ9.99' DEF TOT_PASS (N 15.0) = 0 DEF PCT_PASS (N 3.2) = 0 '% OF TOTAL' 'PASSENGERS' PIC 'ZZ9.99' DEF AVG_PASS (N 10.0) = 0 'AVERAGE VOLUME PER' 'AIRPORT IN COUNTRY' PIC 'Z,ZZZ,ZZZ,ZZ9' DEF COUNT (N 3.0) = 1 ! NOTE ======================================================= NOTE This is the PRESORT section - where you can specify NOTE operations to be carried out before the data is NOTE copied to the hit file and sorted. NOTE ======================================================= ! SET TOT_PASS = A_PASS + TOT_PASS ! NOTE ======================================================= NOTE This is the Report section - one or more reports NOTE may be defined here. NOTE ======================================================= ! REPORT 'WORLD''S 50 BUSIEST AIRPORTS' TITLE 'SORTED BY COUNTRY AND CITY WITH TOTALS' CONTROL (A_COUNTRY) A_CITY ! NOTE ======================================================= NOTE This is the POSTSORT section - where you can specify NOTE operations to be carried out for detail or total NOTE processing after the data has been sorted. NOTE ======================================================= ! SET(D) PCT_PASS = A_PASS / &TOT_PASS * 100 ! IF(D) BREAKQSEQ = 1 DECODE(D) A_COUNTRY INTO COUNTRY 'AU ' = 'Australia ' 'DEN ' = 'Denmark ' 'FRG ' = 'Germany ' 'FR ' = 'France ' 'IT ' = 'Italy ' 'UK ' = 'United Kingdom ' 'NE ' = 'Netherlands ' 'SW ' = 'Switzerland ' 'CAN ' = 'Canada ' 'JA ' = 'Japan ' 'ME ' = 'Mexico ' 'SI ' = 'Singapore ' 'US ' = 'United States of America' ELSE '*COUNTRY NOT IN TABLE*' ELSE SET(D) COUNTRY = ' ' ENDIF ! IF(T) &CTRLBREAK < 2 SET(T) BRK_PASS = A_PASS SET(T) WK_PCT = BRK_PASS / &TOT_PASS * 100 SET(T) BRK_PCT ROUNDED = WK_PCT ELSE SET(T) BRK_PASS = &TOT_PASS SET(T) BRK_PCT = 100.00 ENDIF ! SET(T) AVG_PASS = A_PASS / COUNT ! PRINT COUNTRY A_CITY A_NAME A_PASS PCT_PASS AVG_PASS ! PRINT @A_PASS BRK_PASS @PCT_PASS BRK_PCT ! ! ======================================================= ! This is the required END statement ! ======================================================= END ! This is the last statement of the request
For more information about writing CA-Earl requests, refer to the publications, CA-Earl User Guide and CA-Earl Reference Guide.