Re: recursive WITH nested union ALL with NOCYCLE logic

I have two tables, 1 is a hierarchical table and the other a map to alternative hierarchies. Given a starting node, I need to be able to return the hierarchy and all related hierarchies. 

with

    recursive inn_t(keyv, val, parent) as (

       select * from (

          select key as keyv, val, parent 

             from mike_hier hi where hi.key ='aaa'

          union all

            -- get all alt hierarchies

            select child ,null ,null from mike_map ma  where ma.parent ='aaa' ) gg

    union all

    (

       with xxx as ( select * from inn_t i ) -- only a single reference allowed to inn_t

         select * from 

          (

          select mh.key     , mh.val        , mh.parent 

           from mike_hier mh

           where  mh.parent in (select keyv from xxx) -- normally would join inn_t

          union all

           select child ,null ,null 

           from mike_map ma  

           where ma.parent in (select keyv from xxx)  -- normally would join inn_t

          ) unionall

      )

     ) 

   select distinct * from inn_t where val is not null;