2 Replies Latest reply: Mar 20, 2013 11:52 AM by BluShadow RSS

    Oracle Tree Query(Last Levels)

    Xandot
      please help me out!!!
      How to find the last leafs in oracle tree.
      my tree have only 3 levels so please give me query or any function to sort out this problem.

      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
        • 1. Re: Oracle Tree Query(Last Levels)
          Frank Kulash
          Hi,
          942425 wrote:
          please help me out!!!
          How to find the last leafs in oracle tree.
          my tree have only 3 levels so please give me query or any function to sort out this problem.

          Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
          Sorry, it's unclear what you want.

          Do you want to find the rows that have no children? If so:
          SELECT     ename, empno, mgr     -- or whatever
          FROM     scott.emp
          WHERE     empno     NOT IN  (
                                 SELECT  mgr
                             FROM    scott.emp
                             WHERE   mgr       IS NOT NULL
                             )
          ;
           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          If you can show the problem using commonly available tables (such as those in the scott schema) then you don't need to post any sample data; just the results and the explanation.
          See the forum FAQ {message:id=9360002}

          Edited by: Frank Kulash on Mar 20, 2013 10:35 AM
          • 2. Re: Oracle Tree Query(Last Levels)
            BluShadow
            You can use connect_by_isleaf to check if a node in a hierarchical tree is a leaf node...
            SQL> ed
            Wrote file afiedt.buf
            
              1  select level, empno, lpad(' ',(level-1)*2,' ')||ename as ename
              2  from   emp
              3  connect by mgr = prior empno
              4* start with mgr is null
            SQL> /
            
                 LEVEL      EMPNO ENAME
            ---------- ---------- ------------------------------
                     1       7839 KING
                     2       7566   JONES
                     3       7788     SCOTT
                     4       7876       ADAMS
                     3       7902     FORD
                     4       7369       SMITH
                     2       7698   BLAKE
                     3       7499     ALLEN
                     3       7521     WARD
                     3       7654     MARTIN
                     3       7844     TURNER
                     3       7900     JAMES
                     2       7782   CLARK
                     3       7934     MILLER
            
            14 rows selected.
            
            SQL> ed
            Wrote file afiedt.buf
            
              1  select level, empno, lpad(' ',(level-1)*2,' ')||ename as ename
              2  from   emp
              3  where  connect_by_isleaf = 1 -- Just bring back leaf nodes
              4  connect by mgr = prior empno
              5* start with mgr is null
            SQL> /
            
                 LEVEL      EMPNO ENAME
            ---------- ---------- ------------------------------
                     4       7876       ADAMS
                     4       7369       SMITH
                     3       7499     ALLEN
                     3       7521     WARD
                     3       7654     MARTIN
                     3       7844     TURNER
                     3       7900     JAMES
                     3       7934     MILLER
            
            8 rows selected.