Access an IDMS Bill Of Materials structure with SQL?

Document ID : KB000025177
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

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

Answer:

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 you want to drill, a new alias for the employee record has to be introduced. The alias allows you to represent the Employee record for both sets. Beyond one level it can get very complicated, and we recommend a procedure or table procedure to handle this type of access. 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;