Bhavbhuti,

>Thanks, that's nice. I haven't tried out the code yet but I seem to be getting the point we are looking from the parent in. But one problem I foresee is that I have to know if 7, 8 (via 7) and 9 (via 8, 7) belong to either 4 or 5 or 6.

If you need to do full tree traversal you have a couple of options. One that works very fast if you can constrain the max depth of the tree you can hardwire a number of self joins:

* pnNodeNo = topmost node of the desired subtree

select node.nodeno as n1, ;
      lvl2.nodeno as n2, ;
      lvl3.nodeno as n3, ;
      lvl4.nodeno as n4, ;
      lvl5.nodeno as n5, ;
      lvl6.nodeno as n6, ;
      lvl7.nodeno as n7, ;
      lvl8.nodeno as n8, ;
      lvl9.nodeno as n9, ;
      lvl10.nodeno as n10 ;
   from node ;
   left join node as lvl2  on node.nodeno = lvl2.parentnodeno ;
   left join node as lvl3  on lvl2.nodeno = lvl3.parentnodeno ;
   left join node as lvl4  on lvl3.nodeno = lvl4.parentnodeno ;
   left join node as lvl5  on lvl4.nodeno = lvl5.parentnodeno ;
   left join node as lvl6  on lvl5.nodeno = lvl6.parentnodeno ;
   left join node as lvl7  on lvl6.nodeno = lvl7.parentnodeno ;
   left join node as lvl8  on lvl7.nodeno = lvl8.parentnodeno ;
   left join node as lvl9  on lvl8.nodeno = lvl9.parentnodeno ;
   left join node as lvl10 on lvl9.nodeno = lvl10.parentnodeno ;
   into cursor _subtree ;
   where node.nodeno = pnNodeNo

then you can process the _subtree cursor and put the non-null values into another cursor which is a list of nodeno values.

Or you can go with a recursive function that just iterates the tree however deep it goes.

>Currently I have added a field containing DR or CR (this is still a fresh app so I had the liberty) to avoid this sort of query altogether and I do a replace as soon as the parent for a given node is changed.