From: TOPAZ::BRANTLEY "Douglas Brantley" 17-DEC-1990 01:11:36.00 To: LEDERMAN CC: BRANTLEY Subj: Al Mejia's Solution Las Vegas 4GL Comparison Problem A Solution Using PowerHouse By Alphonse Mejia Senior Systems Analyst Cerritos College Norwalk, California Development of Application 1. I have 7 1/2 years experience as a programmer/analyst. The first 6 years were primarily in COBOL. In the past year and a half about 20% of my programming has been in PowerHouse. 2. It is assumed the user knows the screen modes (Entry & Find) and the action commands ("E"ntry, "F"ind & "A"ppend) required to do the browse and updates. 3. The user would need to be trained in these screen modes and action commands. The user would also need to know how to access the screen which displays the list of fund abbreviations and names. 4. To run this application, a VAX-VMS and PowerHouse run-time license would be required. 5. None of the application is written in a 3GL. 6. The entire application was written in PowerHouse. 7. Time spent developing application: Defining files ... 0:45 Programs to create files ... 0:30 Browse & update program ... 1:00 Fund lookup program ... 0:30 Total 2:45 8. Length of time to find and display a master record and 36 detail records was less than a second. The system was a VAX 6330 using Digital RA90 disk drives and HSC50 controllers. (The sample master browse/update screen was designed to display 17 detail records per screen. The time shown here was reflects tests displaying 36 detail records.) 9. The files were generated using PowerHouse 4GL batch mode processing (QTP). In addition to the indexed files, the data files were defined in the PowerHouse dictionary in the format they were created from the VMS backup save set. This allowed for direct conversion within QTP. Page 2 Development of Application Notes: The solution does not include backward scrolling of master records. This part of the problem can be solved within the PowerHouse 4GL language using "procedural" code. Browse sort order by last name was not included in this solution when searching by soundex of last name. To achieve display of detail records decreasing by date, the primary key of the detail file was defined as descending and including the record-id and date of donation. Files: Page DETAIL-DAT Input to create DETAIL-DAT 3 DETAIL-REC Indexed detail donations 4 FUND-DAT Input to create FUND-REC 5 FUND-REC Indexed Fund abbreviations and names 6 MASTER-DAT Input to create MASTER-REC 7 MASTER-REC Indexed master data 8 Batch programs: BUILD-MASTER.QTS Builds the master file 10 BUILD-DETAIL.QTS Builds the detail file 10 BUILD-FUND.QTS Builds the fund name file 10 UPDATE-MASTER.QTS Master update counts and donations 10 On-line programs: MASTER.QKS Browse/update master and detail 11 FUND-CODES.QKS Display valid funds 13 Page 3 R E C O R D R E P O R T For DICTIONARY: USER_DISK:[AMEJIA.DECUS]DECUS-4GL.PHD; Record: DETAIL-DAT of File: DETAIL-DAT Organization: SEQUENTIAL Type: RMS Open: WORK:DETAIL.DAT Record Format: Fixed Supersede: No Record Size: 21 Bytes -- Record Contents -- Item Type Size Occ Offset ID-NUMBER ZONED UNSIGNED 7 0 DATE-OF-DONATION ZONED UNSIGNED 6 7 .MONTH ZONED UNSIGNED 2 7 .DAYX ZONED UNSIGNED 2 9 .YEAR ZONED UNSIGNED 2 11 FUND-CODE ZONED UNSIGNED 1 13 AMOUNT-OF-DONATION ZONED UNSIGNED 7 14 Page 4 R E C O R D R E P O R T For DICTIONARY: USER_DISK:[AMEJIA.DECUS]DECUS-4GL.PHD; Record: DETAIL-REC of File: DETAIL-REC Organization: INDEXED Type: RMS Open: WORK:DETAL-REC.IDX Record Format: Fixed Supersede: No Record Size: 25 Bytes -- Record Contents -- Item Type Size Occ Offset DETAIL-KEY CHARACTER 13 0 .ID-NUMBER ZONED UNSIGNED 7 0 .DATE-OF-DONATION ZONED UNSIGNED 6 7 ..YEAR ZONED UNSIGNED 2 7 ..MONTH ZONED UNSIGNED 2 9 ..DAYX ZONED UNSIGNED 2 11 FUND-ABBREVIATION CHARACTER 5 13 AMOUNT-OF-DONATION ZONED UNSIGNED 7 18 -- Index Contents -- ** DETAIL-KEY is a 13 byte REPEATING PRIMARY DESCENDING index ** Segment Type Size ID-NUMBER ZONED UNSIGNED 7 DATE-OF-DONATION ZONED UNSIGNED 6 Page 5 R E C O R D R E P O R T For DICTIONARY: USER_DISK:[AMEJIA.DECUS]DECUS-4GL.PHD; Record: FUND-DAT of File: FUND-DAT Organization: SEQUENTIAL Type: RMS Open: WORK:FUNDCODE.DAT Record Format: Fixed Supersede: No Record Size: 56 Bytes -- Record Contents -- Item Type Size Occ Offset FUND-CODE ZONED UNSIGNED 1 0 FUND-ABBREVIATION CHARACTER 5 1 FUND-NAME CHARACTER 50 6 Page 6 R E C O R D R E P O R T For DICTIONARY: USER_DISK:[AMEJIA.DECUS]DECUS-4GL.PHD; Record: FUND-REC of File: FUND-REC Organization: INDEXED Type: RMS Open: WORK:FUND-REC.IDX Record Format: Fixed Supersede: No Record Size: 56 Bytes -- Record Contents -- Item Type Size Occ Offset FUND-CODE ZONED UNSIGNED 1 0 FUND-ABBREVIATION CHARACTER 5 1 FUND-NAME CHARACTER 50 6 -- Index Contents -- ** FUND-CODE is a 1 byte UNIQUE PRIMARY ASCENDING index ** Segment Type Size FUND-CODE ZONED UNSIGNED 1 ** FUND-ABBREVIATION is a 5 byte UNIQUE ALTERNATE ASCENDING index ** Segment Type Size FUND-ABBREVIATION CHARACTER 5 Page 7 R E C O R D R E P O R T For DICTIONARY: USER_DISK:[AMEJIA.DECUS]DECUS-4GL.PHD; Record: MASTER-DAT of File: MASTER-DAT Organization: SEQUENTIAL Type: RMS Open: WORK:MASTER.DAT Record Format: Fixed Supersede: No Record Size: 75 Bytes -- Record Contents -- Item Type Size Occ Offset ID-NUMBER ZONED UNSIGNED 7 0 LAST-NAME CHARACTER 15 7 FIRST-NAME CHARACTER 10 22 MIDDLE-INITIAL CHARACTER 1 32 TITLE-OF-ADDRESS CHARACTER 4 33 SENIORITY CHARACTER 4 37 ADDRESS CHARACTER 15 41 CITY CHARACTER 12 56 STATE CHARACTER 2 68 ZIP-CODE ZONED UNSIGNED 5 70 Page 8 R E C O R D R E P O R T For DICTIONARY: USER_DISK:[AMEJIA.DECUS]DECUS-4GL.PHD; Record: MASTER-REC of File: MASTER-REC Organization: INDEXED Type: RMS Open: WORK:MASTER-REC.IDX Record Format: Fixed Supersede: No Record Size: 94 Bytes -- Record Contents -- Item Type Size Occ Offset ID-NUMBER ZONED UNSIGNED 7 0 LAST-NAME CHARACTER 15 7 LAST-NAME-SOUNDEX CHARACTER 4 22 FIRST-NAME CHARACTER 10 26 MIDDLE-INITIAL CHARACTER 1 36 TITLE-OF-ADDRESS CHARACTER 4 37 SENIORITY CHARACTER 4 41 ADDRESS CHARACTER 15 45 CITY CHARACTER 12 60 STATE CHARACTER 2 72 ZIP-CODE ZONED UNSIGNED 5 74 DETAIL-COUNT ZONED UNSIGNED 4 79 TOTAL-DONATIONS ZONED UNSIGNED 11 83 -- Index Contents -- ** LAST-NAME is a 15 byte REPEATING PRIMARY ASCENDING index ** Segment Type Size LAST-NAME CHARACTER 15 ** ID-NUMBER is a 7 byte UNIQUE ALTERNATE ASCENDING index ** Page 9 R E C O R D R E P O R T For DICTIONARY: USER_DISK:[AMEJIA.DECUS]DECUS-4GL.PHD; Segment Type Size ID-NUMBER ZONED UNSIGNED 7 ** LAST-NAME-SOUNDEX is a 4 byte REPEATING ALTERNATE ASCENDING index ** Segment Type Size LAST-NAME-SOUNDEX CHARACTER 4 Page 10 *** Programs used in building the data files. *** ;Program: BUILD-MASTER.QTS ;Author: Al Mejia ;Date Written: 12-07-90 ;Description: Build the master indexed file from the sequential file. set verify errors noverify delete set input nolimit process nolimit access MASTER-DAT output MASTER-REC add item LAST-NAME-SOUNDEX final soundex(LAST-NAME) go ;Program: BUILD-DEATIL.QTS ;Author: Al Mejia ;Date Written: 12-07-90 ;Description: Build the detail indexed file from the sequential file. set verify errors noverify delete set input nolimit process nolimit access DETAIL-DAT link to FUND-REC output DETAIL-REC add go ;Program: BUILD-FUND.QTS ;Author: Al Mejia ;Date Written: 12-07-90 ;Description: Build the fund indexed file from the sequential file. set verify errors noverify delete set input nolimit process nolimit access FUND-DAT output FUND-REC add go ;Program: UPDATE-MASTER.QTS ;Author: Al Mejia ;Date Written: 12-07-90 ;Description: For each master record, totals and updates count and donations ; of corresponding detail records. set verify errors noverify delete set input nolimit process nolimit access MASTER-REC link to DETAIL-REC sorted on ID-NUMBER output MASTER-REC update at ID-NUMBER item TOTAL-DONATIONS subtotal AMOUNT-OF-DONATION reset at ID-NUMBER item DETAIL-COUNT count at ID-NUMBER reset at ID-NUMBER go Page 11 *** Program used to browse and update master and detail data. ;Program: MASTER.QKS ;Author: Al Mejia ;Date Written: 12-07-90 ;Description: Search for and update master and detail data. set verify errors noverify delete screen MASTER file MASTER-REC access via ID-NUMBER request ID-NUMBER access via LAST-NAME request LAST-NAME access via LAST-NAME-SOUNDEX using soundex(LAST-NAME) request LAST-NAME file DETAIL-REC detail occurs 17 count into DETAIL-COUNT access viaindex DETAIL-KEY via ID-NUMBER using ID-NUMBER of MASTER-REC item LAST-NAME-SOUNDEX final soundex(LAST-NAME) item AMOUNT-OF-DONATION sum into TOTAL-DONATIONS file DETAIL-REC delete alias DELETE-DETAIL access viaindex DETAIL-KEY via ID-NUMBER file FUND-REC reference occurs with DETAIL-REC temp WS-ABBREVIATION char*5 align (1,4,16) (,29,41) field ID-NUMBER of MASTER-REC required nochange lookup noton & MASTER-REC field LAST-NAME-SOUNDEX of MASTER-REC display label "Soundex" align (1,4,16) (,,21) (,,37) (,,48) (,,50) field TITLE-OF-ADDRESS of MASTER-REC label "Name" field LAST-NAME of MASTER-REC field FIRST-NAME of MASTER-REC field MIDDLE-INITIAL of MASTER-REC field SENIORITY of MASTER-REC align (1,4,16) (,,33) (,,47) (,,52) field ADDRESS of MASTER-REC field CITY of MASTER-REC field STATE of MASTER-REC field ZIP-CODE of MASTER-REC align (,4,16) (,29,41) field DETAIL-COUNT of MASTER-REC display field TOTAL-DONATIONS of MASTER-REC display Page 12 *** Program used to browse and update master and detail data continued. skip title "Date" at ,5 title "Amount" at ,16 title "Abbr" at ,24 title "Name" at ,31 align (1,,5) (,,13) (,,24) (,,31) cluster occurs with DETAIL-REC field DATE-OF-DONATION of DETAIL-REC format MMDDYY field AMOUNT-OF-DONATION of DETAIL-REC nochange field FUND-ABBREVIATION of DETAIL-REC lookup on FUND-REC & message "Fund Abbreviation not on file, enter '*' for a list." field FUND-NAME of FUND-REC display procedure input FUND-ABBREVIATION of DETAIL-REC begin if (0 ne index(fieldtext,"*")) then begin clear screen run screen WORK:FUND-CODES passing WS-ABBREVIATION mode F refresh screen let fieldtext = WS-ABBREVIATION end end procedure input LAST-NAME of MASTER-REC if (findmode and not changemode) and (0 ne index(fieldtext,"@")) then let FIELDTEXT = FIELDTEXT + "@" build Page 13 *** Program used to display valid fund codes. ;Program: FUND-CODES.QKS ;Author: Al Mejia ;Date Written: 12-07-90 ;Description: Subscreen to display a list of Fund abbreviations. ; User may enter the id number of a fund on this screen and ; the corresponding fund abbreviation will be returned to the ; calling screen. set verify errors noverify delete screen FUND-CODES receiving WS-ABBREVIATION activities find temp WS-ABBREVIATION char*5 reset at startup file FUND-REC occurs 10 access sequential title "Code" at 2,3 title "Abbrev" at 2,8 title "Description" at 2,16 cluster at 3,1 occurs with FUND-REC align (1,,5) (,,9) (,,17) field FUND-CODE display field FUND-ABBREVIATION display field FUND-NAME display cluster procedure designer 1 begin let WS-ABBREVIATION = FUND-ABBREVIATION return end build