7 Replies Latest reply: Jan 18, 2013 5:29 AM by Stew Ashton RSS

    Connect By Prior Help

    fame
      Hi all,

      In Connect By Prior, How can i avoid, if some parent dont have child means, it should not come in the results.......


      Regards,
      Fame
        • 1. Re: Connect By Prior Help
          Stew Ashton
          A "parent" without a child will not show up as a parent, but it may show up as a child.

          When that happens, CONNECT_BY_ISLEAF will be 1.

          So, filter on CONNECT_BY_ISLEAF = 0.
          select empno, ename, job, mgr from scott.emp
          where connect_by_isleaf = 0
          start with mgr is null
          connect by mgr = prior empno;
          
               EMPNO ENAME      JOB              MGR
          ---------- ---------- --------- ----------
                7839 KING       PRESIDENT            
                7566 JONES      MANAGER         7839 
                7788 SCOTT      ANALYST         7566 
                7902 FORD       ANALYST         7566 
                7698 BLAKE      MANAGER         7839 
                7782 CLARK      MANAGER         7839
          Edited by: Stew Ashton on Jan 17, 2013 1:34 PM
          • 2. Re: Connect By Prior Help
            BluShadow
            Or if the OP is referring to only root parents that have no children then perhaps..
            where not (level = 1 and connect_by_isleaf = 1)
            ?
            • 3. Re: Connect By Prior Help
              fame
              Hi,

              If parent dont have a child means, your query is correct but how can avoid, if parent have child and the child dont have child, how?

              Regards,
              Fame
              • 4. Re: Connect By Prior Help
                Purvesh K
                fame wrote:
                Hi,

                If parent dont have a child means, your query is correct but how can avoid, if parent have child and the child dont have child, how?

                Regards,
                Fame
                You have close to 250 posts on this forum in a span of over an Year. You should be aware of how to Post a thread.

                Mere statements make if difficult to understand the objective of question, hence some data supporting the Question is always helpful.

                Please read {message:id=9360002} and re-post with relevant details and your Best effort.
                • 5. Re: Connect By Prior Help
                  Optimistic
                  do u mean, u want to avoid leaf nodes? Question is not clear enough
                  • 6. Re: Connect By Prior Help
                    chris227
                    only
                    not connect_by_isleaf = 1
                    • 7. Re: Connect By Prior Help
                      Stew Ashton
                      I answered your question here: {message:id=10798393}

                      If my answer is correct, tell me.

                      If my answer is wrong, tell me what is wrong.

                      If you don't understand the answer, ask questions.