This discussion is archived
7 Replies Latest reply: Jan 18, 2013 3:29 AM by Stew Ashton RSS

Connect By Prior Help

fame Explorer
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    do u mean, u want to avoid leaf nodes? Question is not clear enough
  • 6. Re: Connect By Prior Help
    chris227 Guru
    Currently Being Moderated
    only
    not connect_by_isleaf = 1
  • 7. Re: Connect By Prior Help
    Stew Ashton Expert
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points