gistfile1.sql

with recursive AccountHier as 
( select ACCNT_XMLOBJ_ID, ACCNT_NUM, ACCNT_PARENT_ID, 
         ACCNT_NAME, ACCNT_DESC, 
         1 ALevel,    
         cast(Accnt_INum as varchar(20)) SortPath,
         ACCNT_GROUP, 
         ACCNT_ACTPASS, 
         ACCNT_ACTPASSCODE, ACCNT_DETAILLEVEL from Accounts
    where ACCNT_PARENT_ID=0
  union all
  select ACCNT_XMLOBJ_ID, ACCNT_NUM, ACCNT_PARENT_ID, 
         ACCNT_NAME, ACCNT_DESC,  
         ah.ALevel+1 ALevel,
         ah.SortPath||'/'|| cast(Accnt_INum as varchar(20)) SortPath,

         case
           when coalesce(ACCNT_GROUP,'')='' then ah.ACCNT_GROUP
           else ACCNT_GROUP
         end ACCNT_GROUP, 

         case 
           when coalesce(ACCNT_ACTPASS,0)=0 then ah.ACCNT_ACTPASS
           else ACCNT_ACTPASS
         end ACCNT_ACTPASS,

         case 
           when coalesce(ACCNT_ACTPASSCODE,0)=0 then ah.ACCNT_ACTPASSCODE
           else ACCNT_ACTPASSCODE
         end ACCNT_ACTPASSCODE,

         case 
           when coalesce(ACCNT_DETAILLEVEL,0)=0 then ah.ACCNT_DETAILLEVEL
           else ACCNT_DETAILLEVEL
         end ACCNT_DETAILLEVEL         

      from Accounts
    join AccountHier ah on ACCNT_PARENT_ID=ah.ACCNT_XMLOBJ_ID
    order by ACCNT_INUM
)
 select * from AccountHier