CONNECT_BY_ISLEAF AND CONNECT_BY_ROOT

CONNECT_BY_PRIOR CLAUSE

-----------------------------------------------

As we know we that the CONNECT_BY_PRIOR clause is used to get all the employees working under particular manager.

START WITH clause is used to start with all the managers.

Now we will learn about how to use CONNECT_BY_ISLEAF AND CONNECT_BY_ROOT.

This commands are especially used in the hierarchical trees where a child can be parent for another child. We have multiple parent child relations ships .

If we take the Project Accounting module.

The projects will have tasks and these tasks can be Hierarchical.

A child task can have a parent where the child task in turn is the parent for another child task.

Example:

We have a project - 'Test Project'

We have tasks 

10000 Task Reference 1                        -Top Root Task and Parent Task

  10000 Task Reference 2                      --Child task and parent task

     Test Subtask                                     -- child task and parent task

       Subtask2                                         --only child task

  10000 Task Reference 3                     --only child task

Over Head Allocations                          --task

In the above Tree structure , we have multiple parents like '10000 Task Reference 1' and '10000 Task Reference 2 ', but the top parent for all the sub tasks is '10000 Task Reference 1'.

In order to identfy the top task we should use CONNECT_BY_ROOT and in the above structure it returns value '10000 Task Reference 1'.

If we want find all the multiple child tasks which are leaf nodes, that means they do not have any further children then we use CONNECT_BY_ISLEAF.

In the above query if we use CONNECT_BY_ISLEAF it returns value 1 if the current row is leaf node else it returns value 0.

That means the query will return value 1 for nodes like "Subtask2" and "10000 Task Reference 3" and "Over HEad Allocations"as they are leaf nodes they do not have further children.

For rest of the tasks it returns value 0.

Query :

SELECT child_task
       ,parent_task
       ,CONNECT_BY_ISLEAF IS_LOWEST_LEVEL 
       ,CONNECT_BY_ROOT (parent_task)

 FROM

(SELECT pt_child.task_name child_task,

     pt_parent.task_name parent_task

FROM pa_tasks pt_parent,

      pa_tasks pt_child,

        pa_projects_all     PPA

 WHERE pt_parent.project_id(+) = pt_child.project_id

 AND   ppa.project_id =pt_child.project_id

 AND   PPA.project_id =

 AND pt_parent.task_id(+)=pt_child.parent_task_id) inner_query

 CONNECT BY PRIOR inner_query.child_Task=inner_query.parent_task


 START WITH inner_query.parent_Task IS NULL