Required Oracle Query to populate Hierarchies

Question

Background... As part of existing data conversion we need to convert to populate Adhoc Hierarchies with a limited information.

Currently we have a finger countable members from CXO House treated to be users of this Adhoc Hierarchies. They have their choice of Employee Hierarchical combinations for a purpose.

These hierarchies have only Parent - Child relation and can be of at any level to any level of Org Hierarchy. In the other words... child never become a parent unless he has subordinates for him.

 We have an employee table of ORG EmpHierarchy (OH) that has Organizational hierarchies. 
But, which can't be directly used but we can take help of these columns to form our logic. This table is no way related to current model. 

We have a few other tables HeadofDepartment(HOD), HierarchyDetails(HD) and a Stage Table same as HD.

OrgEmpHierarchy (OH) Has:
OH_ID - Organizational HierarchyID (DB Sequence)
OH_PID - (Parent ID) one of the values from previous column.
OH_EmpID - Organizational EmpID.

HeadofDepartment (HOD) Has:
HOD_ID - Head of Dept. ID (DB Sequence)
HOD_EmpID - Organizational EmpID.

HierarchyDetails (HD) Has:
HD_ID - Hierarchy Details ID (DB Sequence)
HD_PID - (ParentID) of of the values from previous column.
HD_HOD_ID - (Foreign Key) HOD_ID from HOD.
HD_EmpID - Organizational EmpID.

We need to populate the Hierarchy for each HOD_ID from Head of Department (HOD) in Hierarchy Details (HD) table. We are able to populate values in HD for HD_ID, HD_HOD_ID, HD_Emp_ID. HD_PID is populated with NULL.

Now with the help of OH, HOD I need to populate Hierarchies in HD_PID in HD table.

Can some one give me a Oracle SQL/ PLSQL query which udpates the HD_PID?

Answer 1

Since this is an assignment I am not going to "give you the SQL".

You will need to use a hierarchical query which has the basic format:

SELECT some_columns
FROM   a_table
START WITH some_condition
CONNECT BY PRIOR some_column = some_other_column

So, which table to select from? Well, looking at your tables OrgEmpHierarchy has contains a relationship between OH_PID (the Parent ID) and OH_EmpID so you should be using this table and connecting on those columns.

So, what should you start with? Without any data, it is difficult to say but presumably the top of the hierarchy is either an Owner/Director or the Head of Departments - if it is that latter case then you can look at starting with those employees that are IN the HeadofDepartment table.

What to select?

• You're using the OH_PID and OH_EmpID columns to connect the query so you can select these as they define the hierarchical relationship.
• You can select a sequence using ROWNUM or use an actual sequence YOUR_SEQUENCE.NEXTVAL.
• You need to know the Head of Department - assuming you start the hierarchy at with those head of departments then there is a very simple operator that lets you "connect by the root" of the hierarchy - I'm sure a simple web search will inform you of its syntax an usage.

That just about covers it all except you'll want to insert it into a table. So, use:

INSERT INTO HierarchyDetails ( columns )
SELECT ...