Example 3—Job Titles of Company Employees

Sample Programs—Database Access › Example 3—Job Titles of Company Employees

Example 3—Job Titles of Company Employees

The input for this example is a logical record that contains the OFFICE, DEPARTMENT, EMPLOYEE, and JOB records shown in Employee Database Subschema. CA Culprit accesses occurrences of the logical record by using key values that correspond to the CALC-key field of the JOB record. The output for the report lists all employees that share the same job id.

CA Culprit performs the following functions in this program:

Accesses a key file that contains values that correspond to the JOB-ID-0440 field in the logical record
Selectively retrieves logical record occurrences that meet the criteria specified in the WHERE clause on the PATH parameter
Sorts the data by department name
Prints the name and location of all employees that share the same job below a detail line that contains the department name associated with the job, the job id, and the job title

The first figure shows the CA Culprit parameters that produced the report shown in the second figure.

Parameters for Employee Job Titles:

DATABASE DICTNAME=DOCANWK IN DB SS=EMPSS09,EMPSCHM,100 KEYFILE 80 KF=1 4 LRFNAME=JOB-ID-0440 DD=SYS002 PATHAA EMP-JOB-LR WHERE JOB-ID-0440 EQ KEY-VALUE AND * (DEPT-NAME-0410 EQ 'COMPUTER OPERATIONS' * OR DEPT-NAME-0410 EQ 'INTERNAL SOFTWARE' * OR DEPT-NAME-0410 EQ 'BRAINSTORMING' * OR DEPT-NAME-0410 EQ 'BLUE SKIES') 01OUT D 01SORT DEPT-NAME-0410 013JOB TITLES OF COMPANY EMPLOYEES 0151*0100DEPT-NAME-0410 HH ' ' 'DEPARTMENT' 'AND' * 'EMPLOYEE NAME' 0151*020 JOB-ID-0440 SZ=4 FN HH 'JOB ID' 'AND' 'OFFICE' 0151*030 TITLE-0440 HH 'TITLE' 0152*010 EMP-NAME-0415 0152*020 OFFICE-CITY-0450 010 JOB-ID 0000 017100 IF JOB-ID-0440 EQ JOB-ID 200 017 MOVE JOB-ID-0440 TO JOB-ID 017 RELS 1 017200 RELS 2

Report for Employee Job Titles:

REPORT NO. 01 JOB TITLES OF COMPANY EMPLOYEES mm/dd/yy PAGE 1 DEPARTMENT JOB ID AND AND EMPLOYEE NAME OFFICE TITLE BLUE SKIES 5037 SUNSHINE SUPERVISOR ALAN DONOVAN GLASSTER BLUE SKIES 5039 CUMULUS CARETAKER BETH M. CLOUD GLASSTER BLUE SKIES 5005 MGR BLUE SKIES DANIEL MOON GLASSTER BRAINSTORMING 5025 SNOWBLOWER RICHARD MUNYON WESTON BRAINSTORMING 5001 MGR BRAINSTORMING RENE MAKER GLASSTER BRAINSTORMING 5027 KEEPER OF THE WINDS S.E.A. BREEZE WESTON BRAINSTORMING 5029 STURM/DRANG ADMIN RICHARD WAGNER WESTON BRAINSTORMING 5021 RAINMAKER BURT LANCHESTER GLASSTER COMPUTER OPERATIONS 3051 DATA ENTRY CLERK SANDY KRAAMER BOSTON ROBIN GARDNER BOSTON GEORGE FONRAD BOSTON COMPUTER OPERATIONS 3003 MGR COMPUTER OPS HERBIE BABIE SPRINGFIELD COMPUTER OPERATIONS 3029 COMPUTER OPERATOR JANE FERNDALE SPRINGFIELD HERBERT LIPSICH SPRINGFIELD COMPUTER OPERATIONS 3023 SYSTEMS PROGRAMMER TERENCE KLWELLEN SPRINGFIELD INTERNAL SOFTWARE 3027 PROGRAMMER TRAINEE NGUSA KARL-I-BOND SPRINGFIELD INTERNAL SOFTWARE 3011 DATABASE ADMIN. JAMES JACOBI SPRINGFIELD INTERNAL SOFTWARE 3001 MGR INTERNL SOFTWARE JENNIFER GARFIELD SPRINGFIELD INTERNAL SOFTWARE 3025 PROGRAMMER/ANALYST JAMES PAKKER SPRINGFIELD PERCY EINSTEIN SPRINGFIELD VLADIMIR HEAROWITZ SPRINGFIELD JANE SOUGH SPRINGFIELD KATHERINE O'HEARN SPRINGFIELD JULIE JANSSEN SPRINGFIELD RECORDS WRITTEN FOR REPORT 01— 52

The parameters coded for this report are described below:

DATABASE and INPUT Parameters

The DATABASE parameter for this run is the same as described for the other examples in this chapter. The INPUT parameter is also the same, except that in this case, the subschema to be accessed is EMPSS09.

KEYFILE Parameter

The code also specifies a KEYFILE parameter, which identifies a sequential file that contains fixed-length 80-byte records. The key-file field begins in column 1 for a length a four bytes; the values in this field are to be compared to the logical record field identified in the LRFNAME= keyword expression on the KEYFILE parameter.

PATH Parameter

The PATH parameter specifies logical record EMP-JOB-LR, followed by a WHERE clause that limits the number of logical record occurrences CA Culprit retrieves, as follows:

CA Culprit retrieves logical record occurrences that specify a value for JOB-ID-0440 that equals the current value in the key-file field.
Of the records that pass the first selection criteria, CA Culprit selects all record occurrences that specify either COMPUTER OPERATIONS, INTERNAL SOFTWARE, BRAINSTORMING, or BLUE SKIES for a department name.

Work Field Definition Parameter

Report 01 specifies a numeric work field, JOB-ID, which is initialized to zero. CA Culprit compares the value of JOB-ID-0440 in the logical record to the value of the work field in order to eliminate duplicate occurrences.

Output Definition Parameters

Report 01 is a printed report that contains details-only information. Following the title, CA Culprit prints headings at the top of each report page from the auto-headers specified on the type 5 edit parameters. To generate a blank line between the title and the headings, the first auto-header literal associated with field DEPT-NAME-0410 contains a single blank.

The detail lines of the report are sorted in alphabetical order by the name of the department associated with each job. The first detail line outputs the name of the department, the job id, and the job title in relative columns *010, *020, and *030, respectively. The spacing code associated with detail line 1 instructs CA Culprit to skip a line before it outputs the data.

The second detail line outputs the name of the employee and the employee's job location in relative columns *010 and *020. CA Culprit prints detail line 2 as long as the value of the JOB-ID-0440 field remains the same.

Process Parameters

The procedure logic for Report 01 compares the value of the JOB-ID-0440 field in the input buffer to the value in the JOB-ID work field. When the value in the input buffer changes, CA Culprit moves the new value to the work field and prints detail lines 1 and 2. If the value remains the same, CA Culprit branches to process statement 200 and prints detail line 2.