After some time I’m back in business and ready to publish a bunch of new articles. In the past months I switched employers and became a dad for the first time so had to change priorities, from Oracle stuff to exploring a new organization and clients and well….. changing diapers 😉 That being said….I’m ready again.
Earlier I posted an article on the use of hierarchical queries on Oracle database, read that article here Hierarchical queries on Oracle database. I left some items open which I’ll explain in this follow-up.
NOCYCLE
When working with hierarchies it can happen that the data you are working with contains loops. This means when building queries with connect by prior the result may be neverending. To stop this we need to use loop detection and for that we have the NOCYCLE keyword. Have a look at the below update sql which will update the manager_id of our top manager Steven King to Hermann Baer with employee_id 204.
update employees
set manager_id = 204
where employee_id = 100;The above SQL will introduce a loop in the data. When you execute a SQL statement with connect by Oracle database will return an ORA-01436: CONNECT BY loop in user data.
select
employee_id
, lpad ( ' ', level, ' ' ) || first_name || ' ' || last_name name
, manager_id
from employees
start with employee_id = 100
connect by prior employee_id = manager_id;
To prevent this, and to tell Oracle database to continue executing when finding a loop we need to add the NOCYCLE keyword to our connect by clause.
select
employee_id
, lpad ( ' ', level, ' ' ) || first_name || ' ' || last_name name
, manager_id
from employees
start with employee_id = 100
connect by nocycle prior employee_id = manager_id;
CONNECT_BY_ROOT
The purpose of connect_by_root is simple. It can display a column from the root row for every row returned. In other words, in our example, we can display the top manager for every employee as a seperate column. Have a look at the below. It will display our top manager Steven King for every row.
select
employee_id
, lpad ( ' ', level, ' ' ) || first_name || ' ' || last_name name
, manager_id
, connect_by_root (first_name || ' ' || last_name) top_manager_full_name
from employees
start with employee_id = 100
connect by prior employee_id = manager_id;
SYS_CONNECT_BY_PATH
sys_connect_by_path will give you the option to display the path which has been taken to come from the root node to your current row. You can add a seperator to every step. Have a look at the below query which will show the path from the root node (our Steven King) to the current row seperated by a pipe character.
select
employee_id
, lpad ( ' ', level, ' ' ) || first_name || ' ' || last_name name
, manager_id
, connect_by_root (first_name || ' ' || last_name) top_manager_full_name
, sys_connect_by_path ( last_name, ' | ') tree_path
from employees
start with employee_id = 100
connect by prior employee_id = manager_id;
CONNECT_BY_ISLEAF
connect_by_isleaf will tell you if you have reached the end of a path. In other words, the current row has no childs anymore. When the end of a path is reached, connect_by_isleaf will return a 1. If the current row still has childs, so the current employee is a manager, connect_by_isleaf will return a 0. Take a look at the below SQL and the results of it.
select
level
, employee_id
, lpad ( ' ', level, ' ' ) || first_name || ' ' || last_name name
, manager_id
, connect_by_root (first_name || ' ' || last_name) top_manager_full_name
, sys_connect_by_path ( last_name, ' | ') tree_path
, connect_by_isleaf is_leaf
from employees
start with employee_id = 100
connect by prior employee_id = manager_id;
With all these kind of queries and keywords you will come up with some practical use of it. As an example I used a combination of these queries to build an extension to OBIEE to display an organization chart with some help of an external JavaScript library. Enjoy!
