Bhavbhuti,

Here's one way:

create cursor tree ( nodeno i, parentnodeno i )
insert into tree values ( 1, 0 )
insert into tree values ( 2, 0 )
insert into tree values ( 3, 0 )
insert into tree values ( 4, 1 )
insert into tree values ( 5, 1 )
insert into tree values ( 6, 1 )
insert into tree values ( 7, 2 )
insert into tree values ( 8, 7 )
insert into tree values ( 9, 8 )
select * ;
   from tree ;
   where tree.parentnodeno in ( select nodeno from tree where parentnodeno = 0 )

the inner select finds all of the root nodes you are interested in and the outer select pulls their child nodes. So the result is 4,5,6,7. Nodes 8 and 9 are not picked because they are 2 and 3 levels respectively away from the root nodes.

>I have to find out the root+1 of each record in a hierarchial table.
>
>Is that possible using SQL. The data contained is for an Accounts tree, so the root nodes are Manufacturing A/c., Profit & Loss A/c., Balance Sheet. The Root+1 nodes will be Debit, Credit (for manu. /a.c), Debit, Credit (for P&L), Asset and Liability (for Bal. Sheet).
>
>The aim is to find out the Debit, Credit, Asset or Liability status of each account.