Recently there is a requirement in my project to display a tree structure from a database. I couldn't explain the exact scenarios in the blog as it has some confidential information. Hence, I am using the scenario which is being used across google.
Consider a scenario where program managers have a list of project managers reporting under them and the project manager have a list of employees under them.
EId | Name | Manager ID
-----------------------------------------
1 | Arun | 2
2 | Balu | 3
3 | Chandan | NULL
------------------------------------------
If we need to display the list of employees under Chandan. The following query will do.
SELECT eid,name,manager_id from employees start with eid-1 CONNECT BY PRIOR eid=manager_id
Consider that there are more than 2 project managers for a same Program manager and each have an Eng. So,we need to know which engineer reports to which manager. In That scenario, SYS_CONNECT_BY_PATH clause would help.
SELECT eid, name, manager_id, SYS_CONNECT_BY_PATH(name, '/') PATH, LEVEL FROM employees start with eid=1 CONNECT BY PRIOR eid = manager id;
This would print the path for Arun as Chandan/Balu/Arun and for Balu it would print as chandan/balu.
LEVEL clause would help in finding the position of the Node in the tree. In the above scenario, Chandan would get 2, Balu - 1 and Arun would get it as 0. These types of Queries are very useful if you need to build in a Hierarchy in your application.
Consider a scenario where program managers have a list of project managers reporting under them and the project manager have a list of employees under them.
EId | Name | Manager ID
-----------------------------------------
1 | Arun | 2
2 | Balu | 3
3 | Chandan | NULL
------------------------------------------
If we need to display the list of employees under Chandan. The following query will do.
SELECT eid,name,manager_id from employees start with eid-1 CONNECT BY PRIOR eid=manager_id
Consider that there are more than 2 project managers for a same Program manager and each have an Eng. So,we need to know which engineer reports to which manager. In That scenario, SYS_CONNECT_BY_PATH clause would help.
SELECT eid, name, manager_id, SYS_CONNECT_BY_PATH(name, '/') PATH, LEVEL FROM employees start with eid=1 CONNECT BY PRIOR eid = manager id;
This would print the path for Arun as Chandan/Balu/Arun and for Balu it would print as chandan/balu.
LEVEL clause would help in finding the position of the Node in the tree. In the above scenario, Chandan would get 2, Balu - 1 and Arun would get it as 0. These types of Queries are very useful if you need to build in a Hierarchy in your application.
No comments :
Post a Comment