Access an IDMS Bill Of Materials structure with SQL

Products

• IDMS
• IDMS - Database

Issue/Introduction

How to code a basic SQL statement to access an IDMS Bill Of Materials structure.

Environment

Release: All supported releases.
Component: SQL Option.

Resolution

Here is an example of a basic SQL select on an IDMS BOM structure against the IDMS Employee demo database. This represents one level of explosion of the BOM structure between the Employee record and the Structure record, using the sets MANAGES and REPORTS-TO. For each level down to drill, a new alias for the employee record has to be introduced. The alias represents the Employee record for both sets. Beyond one level it can get very complicated, and it is recommended that a procedure or table procedure to handle this type of access is used. Schema EMPSQL below is an SQL schema created for network schema EMPSCHM Version 100. This select finds all employees who report to "manager" employee with a first name of "HERBERT".

SET SESSION CURRENT SCHEMA EMPSQL;
SELECT MGR.EMP_FIRST_NAME_0415 AS MGR_1ST_NAME,
       MGR.EMP_LAST_NAME_0415 AS MGR_SURNAME,
       S.STRUCTURE_CODE_0460 AS SC,
       EMP.EMP_FIRST_NAME_0415 AS EMP_1ST_NAME,
       EMP.EMP_LAST_NAME_0415 AS EMP_SURNAME
  FROM EMPLOYEE MGR, STRUCTURE S, EMPLOYEE EMP
 WHERE MGR.EMP_FIRST_NAME_0415 = 'HERBERT'
       AND MANAGES.MGR.S
       AND "REPORTS-TO".S.EMP;