Joining Tables or Database Records › Retrieving Information From CA IDMS/DB Records › Retrieving data from a record joined to itself

Retrieving data from a record joined to itself

By using a nested structure

Like tables, records can participate in nested structures. For example, employees who are supervisors have employees who are staff members. Likewise, employees who are staff can report to more than one supervisor.

This type of set relationship is called a bill-of-materials structure. The data structure diagram in num=A.Sample Tables and Database shows a bill-of-materials structure between the EMPLOYEE and STRUCTURE records:

• One set is MANAGES. It associates supervisors with staff.
• The other set is REPORTS-TO. It associates each employee with one or more supervisors.

The STRUCTURE record exists only to facilitate these set relationships.

Assign alias record names

This figure shows how CA OLQ interprets a bill-of-materials structure relationally, by using alias names for the EMPLOYEE record:

• The SUPERVISOR alias contains occurrences of supervisors. The MANAGES set relates each supervisor to employees who are staff.
• The WORKER alias contains occurrences of staff. The REPORTS-TO set relates each staff member to employees who are supervisors.

CA OLQ uses the concept illustrated in the figure shown under Retrieving data from two or more records to interpret these set relationships:

                    ┌─────────────┐
                    │             │
                    │  EMPLOYEE   │
                    │             │
                    └─┬─────────┬─┘
       MANAGES        │         │        REPORTS TO
                      │         │
                    ┌─▼─────────▼─┐
                    │             │
                    │  STRUCTURE  │
                    │             │
                    └─────────────┘

┌─────────────┐                         ┌───────────────┐
│             │                         │               │
│ SUPERVISOR  │                         │    WORKER     │
│             │                         │               │
└─────┬───────┘                         └───────┬───────┘
      │                                         │
      │ MANAGES                      REPORTS TO │
      │            ┌───────────────┐            │
      │            │               │            │
      └────────────►   EMPLOYEE    ◄────────────┘
                   │               │
                   └───────────────┘

           CA OLQ relates the supervisor IDs
           to worker record occurrences and
           relates worker worker IDs to man-
           ager record occurrences in two
           tables
                          │
                          │
                          ▼
   MANAGES                          REPORTS-TO
┌────────────────────┐            ┌───────────────────────┐
│ MGRID   WORKER     │            │ WORKERID   MANAGER    │
│                    │            │                       │
│ 0075    Poznanski  │            │ 0075       Sarem      │
│ 0075    Achebe     │            │ 0075       Romans     │
└────────────────────┘            └───────────────────────┘
▲                                 ▲
│                                 │
│                                 │
Employee 0075 manages             Employee 0075 works for
Poznanski and Achebe              Sarem and Romans

Coding the SELECT statement

As with a reflexive table join, the SELECT statement is unique in that you assign alias names to the same record. To code the SELECT statement, follow these steps:

1. Qualify each record field name with an alias record name: supervisor.emp-last-name-0415.
2. For each bill-of-materials navigation, assign a unique alias to the record by coding:
   1. The record name
   2. A blank
   3. The alias

   Separate each record and its alias from another with a comma: employee supervisor, employee worker, structure.

3. Name the sets that participate in the bill-of-materials following the WHERE clause by coding:
   1. A qualified set name. A qualified set name is the set name followed by a period and the alias record name: where reports-to.worker.
   2. An AND logical operator.
   3. A second qualified set name.

Example— Listing managers and their staff

Retrieve each project leader and the staff working on the project. The SELECT statement assigns these aliases to the EMPLOYEE record: SUPERVISOR and WORKER. STRUCTURE is the name of the CA IDMS/DB record that facilitates this bill-of-materials data structure.

The alias table names qualify record field names that appear following the SELECT keyword and set names that appear in the WHERE clause:

select supervisor.emp-last-name-0415 as &xq.project leader',
  worker.emp-last-name-0415 as &xq.staff'
  from employee supervisor, employee worker, structure
  where manages.supervisor and reports-to.worker
  order by supervisor.emp-last-name-0415 ! display

                            EMPLOYEE/EMPLOYEE REPORT
                                    mm/dd/yy

                   PROJECT LEADER                    STAFF

                 BANK                           ZEDI
                 BANK                           JACKSON
                 BANK                           PENMAN
                 BANK                           MCDOUGALL
                 BANK                           ANGELO
                 CRANE                          GARDNER
                 CRANE                          KAHALLY
                 CRANE                          KLWELLEN
                 CRANE                          LIPSICH
                 CRANE                          KRAAMER
                 CRANE                          TERNER
                 CRANE                          FONRAD
                 CRANE                          FERNDALE

                                      - 1 -