Sample Programs—Table Access and Creation › Example 2—Creating Tables from Database Records

Example 2—Creating Tables from Database Records

This program reads the data from the Employee database that appears in Figure K-1. As shown in the following figure, EMPSS01 is the view of the database to be accessed, and DOCANWK is the name of the dictionary that stores the database definitions. Since the PATH parameter specifies only a primary path id, CA Culprit will navigate the database and return an occurrence of each record that the PATH parameter specifies.

Parameters Coded for Example 2:

  DATABASE DICTNAME=DOCANWK
  PROFILE USER=LHN PW=ROSEBUD
  INPUT DB SS=EMPSS01,EMPSCHM,100
  PATHAA JOB EMPOSITION EMPLOYEE DEPARTMENT
  01$
  01$             ADD TO TABLE STATUS
  01$
  01OUTPUT TABLE=STATUS TYPE=ADD CATALOG=ASFDICT
  0151*010 EMP-NAME   SZ=25
  0151*020 TITLE      SZ=20
  0151*030 DISABILITY SZ=20
  017      IF STATUS-0415 NE '04' DROP
  017      MOVE EMP-NAME-0415 TO EMP-NAME
  017      MOVE TITLE-0440 TO TITLE
  017      MOVE 'LEAVE-OF-ABSENCE' TO DISABILITY
  010 DISABILITY '                    '
  010 EMP-NAME   '                         '
  010 TITLE      '                    '
  02$
  02$            CREATE TABLE 'JOB SALARIES'
  02$
  02OUTPUT T TABLE='JOB SALARIES' TYPE=REPLACE
 *           CATALOG=ASFDICT ONLINE=YES
  02SORT JOB-ID,+,TITLE
  0251*030 SALARY
  0251*000 NUMBER-OF-EMPLOYEES
  0261*010 JOB-ID                 FM '9999'
  0261*020 TITLE                  SZ=20
  0261*030 SALARY                 SZ=6  FP
  0261*040 NUMBER-OF-EMPLOYEES    SZ=4  FZ
  020      JOB-ID 0
  020      NUMBER-OF-EMPLOYEES 1
  020      SALARY DP=2 0
  020      TITLE '                    '
  027      MOVE JOB-ID-0440 TO JOB-ID
  027      MOVE SALARY-AMOUNT-0420 TO SALARY
  027      MOVE TITLE-0440 TO TITLE
  028      IF LEVL EQ 2 DROP

This program generates two reports: Report 01 adds rows to an existing table and Report 02 creates and populates a table. Each report is discussed separately below.

Adding Rows to STATUS

Table STATUS itemizes the name and job title of employees that are on short- or long-term disability. In Example 1, the rows in STATUS were replaced with rows of current employee information. In this example, the names and titles of employees who are on a leave of absence will be added to the table. As shown in the following figure, CA Culprit adds one row of information to the …

New Contents of Table STATUS:

                  EMP-NAME                               TITLE                             DISABILITY

         JACK      KRAMER                         DATA ENTRY CLERK                    SHORT-TERM DISABILITY
         JOE       NGUYA                          WINTERIZER                          LEAVE-OF-ABSENCE

The OUTPUT parameter instructs CA Culprit to access STATUS, a table owned by LHN and defined in ASFDICT. Since CA Culprit is adding rows to an existing table, the type 5 edit parameters must specify the same field definitions that exist in ASFDICT.

The fields specified on the type 5 edit parameters are work fields because the names of the database fields would make inappropriate column headings. The procedure logic for Report 01 moves the database field values to the work fields once CA Culprit has determined that the employee is on a leave of absence (status code 04).

Creating JOB SALARIES

Report 02 creates table JOB SALARIES, which is owned by LHN and defined in ASFDICT. Each row of the table contains the ID and title of a job within the company, the number of employees that perform the job, and the total salary of these employees.

Contents of Table JOB SALARIES:

                JOB-ID                     TITLE                           SALARY                  NUMBER-OF-EMPLOYEES

                 1001               MGR PERSONNEL                          $80,000.00                         1
                 1023               RECRUITER/INTERVWR                     $39,000.00                         1
                 1051               PERSONNEL CLERK                        $26,500.00                         2
                 2001               MGR ACCTNG/PAYROLL                     $82,000.00                         1
                 2023               ACCOUNTANT                             $45,000.00                         1
                 2025               FINANCIAL ANALYST                      $44,000.00                         1
                 2051               AP CLERK                               $14,000.00                         1
                 2053               AR CLERK                               $14,500.00                         1
                 2055               PAYROLL CLERK                          $15,000.00                         1
                 3001               MGR INTERNL SOFTWARE                  $141,000.00                         2
                 3003               MGR COMPUTER OPS                      $205,000.00                         3
                 3011               DATABASE ADMIN.                       $110,000.00                         2
                 3023               SYSTEMS PROGRAMMER                     $43,000.00                         1
                 3025               PROGRAMMER/ANALYST                    $213,000.00                         6
                 3027               PROGRAMMER TRAINEE                     $58,000.00                         2
                 3029               COMPUTER OPERATOR                      $61,000.00                         3

The code that created JOB SALARIES is discussed below:

• The SORT parameter specifies a numeric work field, JOB-ID, a break code, and an alphanumeric work field, TITLE. CA Culprit executes a control break each time the value of JOB-ID changes and outputs the value in column *010 on a type 6 edit parameter. The break code instructs CA Culprit to perform total time processing without generating any blank rows in the table.

  The SORT parameter specifies TITLE in order to output the sort-key value on a type 6 edit parameter. If TITLE were not coded on the SORT parameter, CA Culprit would output the current value of TITLE at total time.

• The type 5 edit parameters write a record to the extracted items and statistics file, which contains the salary for each employee and a counter. Each time a control break occurs, CA Culprit totals the salaries and number of employees for each job.
• The type 6 edit parameters establish the headings and column definitions for the table. Since the field names in the database are not as meaningful as column headings, the type 6 edit parameters specify work fields into which CA Culprit moves the database field values. The JOB-ID column specifies format code FM, which defines the column as text rather than as numeric values. The SALARY column, however, is defined as a 6-byte packed decimal numeric field and the NUMBER-OF-EMPLOYEES column is defined as a 4-byte zoned decimal field.
• Report 02 defines four work fields. Three of these fields, JOB-ID, SALARY, and TITLE receive database field values within the type 7 procedure logic of the report. NUMBER-OF-EMPLOYEES is defined as a numeric work field with a value of 1. The field is specified on both a type 5 and a type 6 edit parameter. When the JOB-ID field changes, CA Culprit outputs the total number of records (employees) processed for that particular control break.
• Report 02 specifies both type 7 and type 8 process parameters. The type 7 process parameters move the database field values into work fields; the type 8 process parameter outputs rows to the table when the job-id value changes on the SORT parameter. The table will not contain a row for the grand total.