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!

Recursive

Discussion in 'SQL PL/SQL' started by kiekar, May 21, 2015.

  1. kiekar

    kiekar Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Recursive Query Help Needed

    Hello,
    First I would like to say that I only have basic SQL skills. I have one table PN_NEXT_LOWER_ASSEMBLY with two fields NHA_PN and NLA_PN that I need to work with that holds hierarchy data. The DB is Oracle but I’m not sure what version. What I do know is that the most upper hierarchy PN is 71-20002-1 and that there is 4 levels down from there.
    e.g.
    71-2002-1
    2
    2
    2
    2 missing P/N from the output screen
    3
    3
    3 missing P/N from the output screen
    4
    4
    3 missing P/N from the output screen
    3
    4
    3

    I was told by someone that recursive CTE would be the way to go so I created this query which works but I’m missing all NHA P/Ns that have NLA under it. I would also like to have the 4 levels displayed also in the output. Why are the NHA P/Ns not showing. Any help would be much appreciated.

    Thanks

    SELECT NHA_PN, CONNECT_BY_ROOT NLA_PN
    FROM PN_NEXT_LOWER_ASSEMBLY
    WHERE NHA_PN = '71-20002-1'
    START WITH
    NLA_PN NOT IN
    (
    SELECT NHA_PN
    FROM PN_NEXT_LOWER_ASSEMBLY
    )
    CONNECT BY
    NLA_PN = PRIOR NHA_PN
     
  2. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello Keikar,

    Could you please provide table creation and data insertion script . That will help us to look into that.

    Can you please provide expected output.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  4. kiekar

    kiekar Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hello and thanks to both of you for your replies.

    That's not possilbe as I'm only using tools available to me from a large app. we use.

    I altered the query slightly from my initial question to

    Code (Text):

    SELECT nha_pn,
                nla_pn,
                level,
                connect_by_root nla_pn AS root_nla_pn
    FROM   pn_next_lower_assembly
    WHERE  nha_pn = '49-20005-1'
    CONNECT BY PRIOR nha_pn = nla_pn
     
    Which now give me the proper output I need except for the hierarchy order

    Output

    nha_pn root_nla_pn level
    49-20005-1 332228050 2
    49-20005-1 4100837G 2
    49-20005-1 4101099H 2
    49-20005-1 4500001C 1
    49-20005-1 4500039F 2
    49-20005-1 4501054N 2
    49-20005-1 4501089A 3
    49-20005-1 4501230A 3
    49-20005-1 4501259C 2
    49-20005-1 4502620B 3
    49-20005-1 4503907 3
    49-20005-1 4504086 2
    49-20005-1 4504433A 2
    49-20005-1 4504834 3
    49-20005-1 4504836 3
    49-20005-1 4505474 4
    49-20005-1 4505476 4
    49-20005-1 4505477A 3
    49-20005-1 4506061 2
    49-20005-1 4507935 3
    49-20005-1 4508059 3
    49-20005-1 4508693 2
    49-20005-1 4950000 2
    49-20005-1 4950036 2
    49-20005-1 4951652 2
    49-20005-1 4952164 2
    49-20005-1 5908661 2
    49-20005-1 5908954 2
    49-20005-1 5908954 2
    49-20005-1 5913667-4 1
    49-20005-1 756407M 2
    49-20005-1 756669C 2
    49-20005-1 756722B 2
    49-20005-1 757288A 2
    49-20005-1 M4503908-8 2


    Proper Hierarcy

    nha_pn root_nla_pn level
    49-20005-1 4500001C 1
    49-20005-1 332228050 2
    49-20005-1 4100837G 2
    49-20005-1 4101099H 2
    49-20005-1 4500039F 2
    49-20005-1 4501054N 2
    49-20005-1 4501259C 2
    49-20005-1 4504086 2
    49-20005-1 4504433A 2
    49-20005-1 4504836 3
    49-20005-1 4506061 2
    49-20005-1 4508693 2
    49-20005-1 4950000 2
    49-20005-1 4950036 2
    49-20005-1 4951652 2
    49-20005-1 4952164 2
    49-20005-1 5908661 2
    49-20005-1 5908954 2
    49-20005-1 5908954 2
    49-20005-1 756407M 2
    49-20005-1 756669C 2
    49-20005-1 756722B 2
    49-20005-1 757288A 2
    49-20005-1 M4503908-8 2
    49-20005-1 4501089A 3
    49-20005-1 4501230A 3
    49-20005-1 4502620B 3
    49-20005-1 4503907 3
    49-20005-1 4505474 4
    49-20005-1 4504834 3
    49-20005-1 4505477A 3
    49-20005-1 4505476 4
    49-20005-1 4507935 3
    49-20005-1 4508059 3
    49-20005-1 5913667-4 1

    How can I accomplish the correct hierarcy output. Your help would be much appreciated

    Regards,
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Without a 'start with' clause you get the order as the parent records appear. You need to find a good starting point to establish the hierarchy order; sometimes the connect by column can be NULL for a starting record but there may be some other criteria you can use to define the 'top' of the tree.
     
  6. kiekar

    kiekar Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Going back to my query

    Code (Text):

    SELECT rownum,
           nha_pn,
           connect_by_root nla_pn AS nla_pn,
           level
    FROM
        pn_next_lower_assembly
    WHERE
        nha_pn = '49-20005-1'
    CONNECT BY
        nla_pn = PRIOR nha_pn
     
    I'm getting the proper output but unfortunately I have no control how it's outputted. I'm using a tool (quick print) available to me from a large app at work.
    What I did notice from the output is the Nla_Pn column the P/Ns are already sorted in ascending order and that the Level column is out of sink. I'm just not sure if my query is causing the sorting or it's been sorted in the back ground.

    From what I read is if one uses the Order By clause the child parent order can be broken and that one must use the Order Siblings By clause.

    I tried the Start With but was unable to get the proper output. I am by now means a guru in SQL I just know basic stuff.

    are you referring to the field connect_by_root or connect by
     
  7. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Try the following:

    Code (Text):
    SELECT rownum,
           nha_pn,
           connect_by_root nla_pn AS nla_pn,
           level
    FROM
        pn_next_lower_assembly
    START WITH
        nha_pn = '49-20005-1'
    CONNECT BY
        nla_pn = PRIOR nha_pn
     
  8. kiekar

    kiekar Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    I tried the START WITH nha_pn = '49-20005-1' but this time it only outputted 2 rows instead of 35 rows

    Code (Text):

    SELECT rownum,
           nha_pn,
           connect_by_root nla_pn AS nla_pn,
           level
    FROM
        pn_next_lower_assembly
    START WITH
        nha_pn = '49-20005-1'
    CONNECT BY
        nla_pn = PRIOR nha_pn.
     
    1 49-20005-1 4500001C 1
    2 49-20005-1 5913667 1
     
  9. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Without knowing the data or having access to the data & tables, it is very difficult to be able to guess the correct syntax for a hierarchical query.
    Try reversing the CONNECT BY PRIOR direction.

    Code (Text):
    SELECT rownum,
           nha_pn,
           connect_by_root nla_pn AS nla_pn,
           level
    FROM
        pn_next_lower_assembly
    START WITH
        nha_pn = '49-20005-1'
    CONNECT BY
        nha_pn = PRIOR nla_pn
     
  10. kiekar

    kiekar Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Thank you ocprep,

    I finall got the output I needed with your help. I altered the query to

    Code (Text):

    SELECT  rownum,
        connect_by_root nha_pn AS nha_pn,
        nla_pn,
        level
    FROM
        pn_next_lower_assembly
    START WITH
        nha_pn = '49-20005-1'
    CONNECT BY
        nha_pn = PRIOR nla_pn
     
    I also changed the connect_by_root to nha_pn from nla_pn