Sample Programs—Database Access › Example 2—Titles and Skills By Department

Example 2—Titles and Skills By Department

CA Culprit enters the database by using the CALC-key value for the DEPARTMENT record specified on a KEY parameter. CA Culprit then navigates a bill-of-materials structure that returns the manager and staff members within the department.

The first figure shows the CA Culprit code that created the report shown in the scond figure.

Parameters for Department Brainstorming:

 DATABASE DICTNAME=DOCANWK
 IN DB SS=EMPSS01,EMPSCHM,100
 KEY DEPT-ID-0410 5100
 PATHAA DEPARTMENT EMPLOYEE STRUCTURE (MANAGES)
*       EMPLOYEE (REPORTS-TO)
 PATH-- EXPERTISE SKILL EMPOSITION JOB
 SELECT EMPLOYEE 2 IN PATH AA WHEN STATUS-0415 EQ '01'
 01OUT 100 D
 01SORT EMP-NAME-0415 (1)
 013MANAGER AND STAFF OF DEPARTMENT BRAINSTORMING
 0141*0010'MANAGER'
 0141*010 EMP-NAME-0415 (1)
 0142*020 'TITLE AND'
 0143*010 'STAFF'
 0143*020 'SKILL LEVEL'
 0143*030 'SKILL'
 0151*0100EMP-NAME-0415 (2)
 0151*020 TITLE-0440
 0152*020 SKILL-LEVEL-0425
 0152*030 SKILL-NAME-0455
 017      CALL DB-EXIT ('NEXT' 'EMPOSITION ' 'EMP-EMPOSITION ')
 017      IF IDMS-STATUS NE '0000' DROP
 017      CALL DB-EXIT ('OWNER' 'JOB ' 'JOB-EMPOSITION ')
 017      IF IDMS-STATUS NE '0000' DROP
 017      RELS 1
 017010   CALL DB-EXIT ('NEXT' 'EXPERTISE ' 'EMP-EXPERTISE ')
 017      IF IDMS-STATUS EQ '0307' TAKE
 017      IF IDMS-STATUS NE '0000' DROP
 017      CALL DB-EXIT ('OWNER' 'SKILL ' 'SKILL-EXPERTISE ')
 017      IF IDMS-STATUS NE '0000' DROP
 017      RELS 2
 017      B  010

Report for Department Brainstorming:

 REPORT NO. 01              MANAGER AND STAFF OF DEPARTMENT BRAINSTORMING        mm/dd/yy PAGE     1

       MANAGER       RENE      MAKER
                                                          TITLE AND
                               STAFF                     SKILL LEVEL               SKILL

                     S.E.A.    BREEZE                KEEPER OF THE WINDS
                                                              04                WIND

                     RAY       ANDOVER               SNOWBLOWER
                                                              04                LIGHTNING
                                                              02                THUNDER
                                                              02                HURRICANE
                                                              02                TORNADO
                                                              02                TIDAL WAVE

                     RICHARD   WAGNER                STURM/DRANG ADMIN
                                                              04                HURRICANE
                                                              04                OPERA
                                                              04                THUNDER

                     HARRY     HARM                  STURM/DRANG ADMIN
                                                              04                THUNDER

                     RICHARD   MUNYON                SNOWBLOWER
                                                              04                WIND
                                                              04                SNOW
                                                              03                COLD

                     CAROLYN   CROW                  RAINDANCE CONSULTANT
                                                              04                RAIN
                                                              04                CHOREOGRAPHY

                     BURT      LANCHESTER            RAINMAKER
                                                              04                DRIZZLE
                                                              04                RAIN
                                                              03                FOG

   RECORDS WRITTEN FOR REPORT 01—        29

Parameters coded for the report are described below:

DATABASE and INPUT Parameters

The DATABASE and INPUT parameters for this CA Culprit run are the same as defined in Example 1 earlier in this chapter.

KEY Parameter

The KEY parameter limits the number of DEPARTMENT records to be processed in the CA Culprit run to 1. The parameter identifies the CALC-key field of the DEPARTMENT record, DEPT-ID-0410, and a numeric value, 5100. CA Culprit directly accesses the DEPARTMENT record occurrence that has 5100 for the DEPT-ID-0410 field and continues to navigate the database according to the route defined on the PATH parameter.

PATH Parameters

The CA Culprit code contains an automatic retrieval and a dummy PATH parameter. Each input buffer that is processed in the CA Culprit run contains an occurrence of the records that define path AA. The EMPLOYEE STRUCTURE (MANAGES) EMPLOYEE (REPORTS-TO) portion of path AA defines a bill-of-materials structure in which the first EMPLOYEE record occurrence contains the manager of the employee named in the second EMPLOYEE record occurrence.

Each input buffer also reserves enough space to accommodate the records named on the dummy PATH parameter, identified by two hyphens (--). CA Culprit retrieves occurrences of these records when it encounters a call to DB-EXIT.

SELECT Parameter

The SELECT parameter applies selection criteria to the second EMPLOYEE record in path AA. If the EMPLOYEE record fails the selection criteria, the input buffer is not processed. In this example, CA Culprit selects all occurrences of the second EMPLOYEE record that specify 01 for the employee status; this value indicates that the employee is actively employed.

Output Definition Parameters

The output for Report 01 is a printed report that contains details-only information. The line size for the report is 100 characters.

The SORT parameter specifies a field associated with the first EMPLOYEE record in the input buffer since the field name is qualified by level number 1. In this example, the SORT parameter is required because EMP-NAME-0415 is specified on a header line.

Report 01 contains a title and three header lines. A spacing code associated with header line 1 generates a blank line between the title and the headings. Report 01 also specifies two types of detail lines. The first contains the name and title of an employee that reports to the manager named in the heading; the second identifies the skills and the skill levels that the staff member has mastered.

Process Parameters

The procedure logic issues four calls to DB-EXIT in order to retrieve the job title, skill level, and skills of each staff member. After each call, the procedure logic tests the value of the IDMS-STATUS field to determine whether the database record was retrieved successfully, as follows:

• The first DB-EXIT call retrieves an occurrence of the EMPOSITION record that is associated with the second EMPLOYEE record occurrence currently in the input buffer. CA Culprit stops processing the input buffer if the IDMS-STATUS code for the call is anything other than 0000.
• The second DB-EXIT call retrieves an occurrence of the JOB record that is the owner of the EMPOSITION record occurrence. The JOB record contains the field TITLE-0440, which contains job title values. Once CA Culprit retrieves the JOB record, it prints detail line 1.
• The third DB-EXIT call is similar to the first, except in this case, CA Culprit retrieves an occurrence of the EXPERTISE record that relates to the second EMPLOYEE record occurrence in the input buffer. Since an employee may have more than one skill level, identified as the field SKILL-LEVEL-0425, CA Culprit iterates through the procedure logic until it encounters an end-of-set condition, identified as status code 0307.
• The fourth DB-EXIT call is similar to the second. CA Culprit retrieves an occurrence of the SKILL record that is the owner of the EXPERTISE record. The SKILL record contains the field SKILL-NAME-0455, which contains the names of various skills. Provided the IDMS-STATUS code is 0000, CA Culprit prints detail line 2 and branches to the process statement that issues a DB-EXIT call for the next EXPERTISE record occurrence.