Oracle Tree Operations

Select  with by ... prior

1. Find all the immediate child nodes of a node (the node itself + all descendants), with the following two ways.

Select ID, TypeName, TypeCode, ParentID, Ordernum  from Purchase_type where = 1  with ='+' by =  Prior ID
Order  by Ordernum

Or

Select ID, TypeName, TypeCode, ParentID, Ordernum  from Purchase_type where = 1  with ='+' by   ParentID  order by Ordernum

Show Results:

2. Find all the immediate parent nodes of a node (the node itself + all ancestors (parent class of Parent class ...). ））。

Select ID, typename, TypeCode, ParentID, Ordernum
From Purchase_type
where status = 1
Start with id = ' 136 '
Connect by id = Prior ParentID
Order siblings by Ordernum

Or

Select ID, TypeName, TypeCode, ParentID, Ordernum  from Purchase_type where = 1 with ='136' by           ID    order by Ordernum

Show Results:

Oracle Tree Operations