1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to use sys_connect_by_path

Discussion in 'SQL PL/SQL' started by Vik@s, Dec 15, 2016.

  1. Vik@s

    Vik@s Active Member

    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    India
    In Sql Query show like
    main manger is repeated with employee

    Main Emp Name
    -------------------------
    Rock max
    Rock king
    Rock
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Exactly how does this explain how to use sys_connect_by_path? I see no examples. A proper post for that title would include information like the following:

    Code (SQL):
    CREATE TABLE BRANCHES(PARENT_NODE VARCHAR(10), CHILD_NODE VARCHAR(10))
       
    INSERT INTO BRANCHES VALUES (' ','LEVEL1');  
    INSERT INTO BRANCHES VALUES ('LEVEL1','LEVEL2');
    INSERT INTO BRANCHES VALUES ('LEVEL2','LEVEL3.1');
    INSERT INTO BRANCHES VALUES ('LEVEL2','LEVEL3.2');
    INSERT INTO BRANCHES VALUES ('LEVEL2','LEVEL3.3');
    INSERT INTO BRANCHES VALUES ('LEVEL3.1','LEVEL4.1');
    INSERT INTO BRANCHES VALUES ('LEVEL3.1','LEVEL4.2');
    INSERT INTO BRANCHES VALUES ('LEVEL3.3','LEVEL4.3');
    INSERT INTO BRANCHES VALUES ('LEVEL3.3','LEVEL4.4');
    INSERT INTO BRANCHES VALUES ('LEVEL4.1','LEVEL5.1');
    INSERT INTO BRANCHES VALUES ('LEVEL4.1','LEVEL5.2');
    INSERT INTO BRANCHES VALUES ('LEVEL4.2','LEVEL5.3');
    INSERT INTO BRANCHES VALUES ('LEVEL4.2','LEVEL5.4');
    INSERT INTO BRANCHES VALUES ('LEVEL4.3','LEVEL5.5');
    INSERT INTO BRANCHES VALUES ('LEVEL4.3','LEVEL5.6');
    INSERT INTO BRANCHES VALUES ('LEVEL4.4','LEVEL5.7');
    INSERT INTO BRANCHES VALUES ('LEVEL4.4','LEVEL5.8');
    INSERT INTO BRANCHES VALUES ('LEVEL5.2','LEVEL6.1');
    INSERT INTO BRANCHES VALUES ('LEVEL5.2','LEVEL6.2');
    INSERT INTO BRANCHES VALUES ('LEVEL5.3','LEVEL6.3');
    INSERT INTO BRANCHES VALUES ('LEVEL5.3','LEVEL6.4');
    INSERT INTO BRANCHES VALUES ('LEVEL5.4','LEVEL6.5');

    commit;

    SELECT SYS_CONNECT_BY_PATH(CHILD_NODE, ' - ')
        FROM BRANCHES
       START WITH PARENT_NODE = ' '
       CONNECT BY prior CHILD_NODE = PARENT_NODE

    SYS_CONNECT_BY_PATH(CHILD_NODE,'-')
    --------------------------------------------------------------------------------
    - LEVEL1
    - LEVEL1 - LEVEL2
    - LEVEL1 - LEVEL2 - LEVEL3.1
    - LEVEL1 - LEVEL2 - LEVEL3.1 - LEVEL4.1
    - LEVEL1 - LEVEL2 - LEVEL3.1 - LEVEL4.1 - LEVEL5.1
    - LEVEL1 - LEVEL2 - LEVEL3.1 - LEVEL4.1 - LEVEL5.2
    - LEVEL1 - LEVEL2 - LEVEL3.1 - LEVEL4.1 - LEVEL5.2 - LEVEL6.1
    - LEVEL1 - LEVEL2 - LEVEL3.1 - LEVEL4.1 - LEVEL5.2 - LEVEL6.2
    ...
     
    I am still wondering how your post illustrates the concept you've tried to introduce.
     
  3. Vik@s

    Vik@s Active Member

    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    India
    Thanks zargon
     
  4. David stephan

    David stephan Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Germany


    SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path"
    FROM employees
    START WITH last_name = 'Rock'
    CONNECT BY PRIOR employee_id = manager_id;

    Path
     
  5. Vik@s

    Vik@s Active Member

    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    India
    Thanks David