4 Replies Latest reply: Apr 4, 2013 2:55 PM by chris227 RSS

    Hierarchical function - help

    1001135
      Hello,


      create table t ( son number, dad number);
      insert into t values ( 1,3);
      insert into t values ( 2,3);
      insert into t values ( 10,3);
      insert into t values ( 3,4);
      insert into t values ( 4,50);
      insert into t values ( 5,80);
      commit;

      How can i get The ancestor of each son, like this :

      son ancestor
      ----- ------------
      1 50
      2 50
      3 50
      4 50
      5 80
      10 50

      Many thanks.
        • 1. Re: Hierarchical function - help
          chris227
          select
           son
          ,dad
          ,connect_by_root(dad) ancestor
          from t
          connect by
          dad = prior son
          start with dad not in (
          select son from t)
          
          SON     DAD     ANCESTOR
          4     50     50
          3     4     50
          1     3     50
          2     3     50
          10     3     50
          5     80     80
          • 2. Bottom-Up Query
            Frank Kulash
            Hi,

            Welcome to the forum!

            Here's one way:
            SELECT     CONNECT_BY_ROOT son     AS son
            ,     dad               AS ancestor
            FROM     t
            WHERE     CONNECT_BY_ISLEAF     = 1
            CONNECT BY     son     = PRIOR dad
            ;
            Notice that there is no START WITH clause.

            Thanks for posting the CREATE TABLE and INSERT statements; that's very helpful!
            Always say which version of Oracle you're using. This is always important, but especially so with hierarchical queries, because every version since Oracle 7 has had major improvements in this area.
            • 3. Re: Bottom-Up Query
              1001135
              Waooo, I'm very happy to find finally a solution.
              Many thanks frank.
              See you soon.
              • 4. Re: Bottom-Up Query
                chris227
                998132 wrote:
                Waooo, I'm very happy to find finally a solution.
                Sorry, that makes no sense to me.
                with this construct
                
                CONNECT BY     son     = PRIOR dad
                
                50 is not an ancestor (resp. root) but a leaf,
                what is emphasized by
                
                CONNECT_BY_ISLEAF