Thread: Oracle Tree

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    1
    Rep Power
    0

    Oracle Tree


    ---------------------------script of table for tree--------------
    create table eqpt_mast
    (
    EQPT_ID VARCHAR2(16),
    EQPT_CODE VARCHAR2(9),
    PARENT_CODE VARCHAR2(9),
    CODE_TYPE NUMBER(1),
    )
    ---------------------------------------------
    insert into eqpt_mast values('','GRANDF','','0');
    insert into eqpt_mast values('FATHER1','','GRANDF','1');
    insert into eqpt_mast values('FATHER2','','GRANDF','1');
    insert into eqpt_mast values('FATHER3','','GRANDF','1');
    insert into eqpt_mast values('CHILD1','','FATHER1','2');
    insert into eqpt_mast values('CHILD2','','FATHER1','2');
    insert into eqpt_mast values('CHILD4','','FATHER3','2');
    insert into eqpt_mast values('CHILD5','','FATHER3','2');
    -------------------------------------------------

    output of tree like:-


    grandf(0)
    |__
    ---father1(1)
    |_____
    --------- child1(2)
    -------- child2(3)
    --- father2(1)
    |_____
    --------- child3(2)
    --- father3(1)
    |_____
    --------- child4(2)
    --------- child5(2)

    ------------------------------------------------------
    i want to make tree like the above format.
    Please give me the query or any suggestions ???????
    In the above tree "(value)" shows the code_type i.e. level of the tree..

    version----
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    Check out CONNECT BY, there are millions of example in the web.

    And please format your SQL using [code] tags.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    1
    Rep Power
    0

    script of table for tree


    Originally Posted by Ankit Bhatt
    ---------------------------script of table for tree--------------
    create table eqpt_mast
    (
    EQPT_ID VARCHAR2(16),
    EQPT_CODE VARCHAR2(9),
    PARENT_CODE VARCHAR2(9),
    CODE_TYPE NUMBER(1),
    )
    ---------------------------------------------
    insert into eqpt_mast values('','GRANDF','','0');
    insert into eqpt_mast values('FATHER1','','GRANDF','1');
    insert into eqpt_mast values('FATHER2','','GRANDF','1');
    insert into eqpt_mast values('FATHER3','','GRANDF','1');
    insert into eqpt_mast values('CHILD1','','FATHER1','2');
    insert into eqpt_mast values('CHILD2','','FATHER1','2');
    insert into eqpt_mast values('CHILD4','','FATHER3','2');
    insert into eqpt_mast values('CHILD5','','FATHER3','2');
    -------------------------------------------------

    output of tree like:-


    grandf(0)
    |__
    ---father1(1)
    |_____
    --------- child1(2)
    -------- child2(3)
    --- father2(1)
    |_____
    --------- child3(2)
    --- father3(1)
    |_____
    --------- child4(2)
    --------- child5(2)

    ------------------------------------------------------
    i want to make tree like the above format.
    Please give me the query or any suggestions ???????
    In the above tree "(value)" shows the code_type i.e. level of the tree..

    version----
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    may this query will reach you expectation....

    select EQPT_ID ,EQPT_CODE,PARENT_CODE,CODE_TYPE from eqpt_mast
    connect by prior EQPT_CODE=PARENT_CODE ;
    Better to go through attached url .It will give more information..
    philip.greenspun.com/sql/trees.html
    Last edited by bujjir; April 18th, 2013 at 07:07 AM. Reason: For more information..

IMN logo majestic logo threadwatch logo seochat tools logo