This discussion is archived
5 Replies Latest reply: Jan 3, 2013 1:00 PM by Solomon Yakobson RSS

hierarchical query

879994 Newbie
Currently Being Moderated
Hi Gurus,
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production


CREATE TABLE TEST_1(TERM VARCHAR2(100), RELATION_NO VARCHAr2(100))

INSERT INTO TEST_1
SELECT * FROM 
  ( SELECT 'Musculoskeletal' TERM,  'A02' RELATION_NO FROM DUAL
        UNION ALL
        SELECT 'Cartilage','A02.165' FROM DUAL
        UNION ALL
        SELECT 'Elastic Cartilage',    'A02.165.257' FROM DUAL
        UNION ALL
        SELECT 'Ear Cartilage' ,   'A02.165.257.250' FROM DUAL
        UNION ALL
        SELECT 'Laryngeal Cartilages',    'A02.165.257.625' FROM DUAL
        UNION ALL
        SELECT 'Arytenoid Cartilage',    'A02.165.257.625.083' FROM DUAL
        UNION ALL
        SELECT 'Cricoid Cartilage',    'A02.165.257.625.211' FROM DUAL
        UNION ALL
        SELECT 'Epiglottis',        'A02.165.257.625.411' FROM DUAL
        UNION ALL
        SELECT 'Thyroid Cartilage',    'A02.165.257.625.870' FROM DUAL
    )
    
So, the data from above insert looks like as follows

TERM               RELATION_NO

Musculoskeletal      A02
Cartilage          A02.165

Elastic Cartilage     A02.165.257
Ear Cartilage          A02.165.257.250
Laryngeal Cartilages     A02.165.257.625

Arytenoid Cartilage     A02.165.257.625.083
Cricoid Cartilage     A02.165.257.625.211
Epiglottis          A02.165.257.625.411
Thyroid Cartilage     A02.165.257.625.870

Note: Space left in the data for better readability. 


Result I want :-


PARENT TERM            CHILD TERM          PARENT RELATION_NO     CHILD RELATION_NO

Musculoskeletal        Cartilage                 A02               A02.165
Cartilage            Elastic Cartilage       A02.165               A02.165.257
Elastic Cartilage       Ear Cartilage          A02.165.257          A02.165.257.250     
Elastic Cartilage     Laryngeal Cartilages    A02.165.257          A02.165.257.625
Laryngeal Cartilages    Arytenoid Cartilage     A02.165.257.625          A02.165.257.625.083
Laryngeal Cartilages    Cricoid Cartilage     A02.165.257.625          A02.165.257.625.211
Laryngeal Cartilages    Epiglottis          A02.165.257.625          A02.165.257.625.411
Laryngeal Cartilages    Thyroid Cartilage     A02.165.257.625          A02.165.257.625.870
1. Parent's RELATION_NO always start without dot (e.g. A02, B01 etc..)
2. Child RELATION_NO always contains dot (e.g. A02.165 etc..)
3. In the above example, I arranged the RELATION_NO in the ascending order, but in real data, RELATION_NO is not arranged in any order.
4. Only one level hierarchical result is needed, parent and child. No grandparent relation need to determine.
5. The longest RELATION_NO in actual data is B01.050.150.900.649.801.400.112.199.120.120.110

Any help would be a great appreciation

Thanks in advance

Edited by: 876991 on Jan 3, 2013 12:14 PM
  • 1. Re: hierarchical query
    Solomon Yakobson Guru
    Currently Being Moderated
    column "PARENT TERM" format a30
    column "CHILD TERM" format a30
    column "PARENT RELATION_NO" format a30
    column "CHILD RELATION_NO" format a30
    select  prior term "PARENT TERM",
            term "CHILD TERM",
            prior relation_no "PARENT RELATION_NO",
            relation_no "CHILD RELATION_NO"
      from  test_1
      where prior term is not null
      start with instr(relation_no,'.') = 0
      connect by prior relation_no = substr(relation_no,1,instr(relation_no,'.',-1) - 1)
    /
    
    PARENT TERM                    CHILD TERM                     PARENT RELATION_NO             CHILD RELATION_NO
    ------------------------------ ------------------------------ ------------------------------ -------
    Musculoskeletal                Cartilage                      A02                            A02.165
    Cartilage                      Elastic Cartilage              A02.165                        A02.165.257
    Elastic Cartilage              Ear Cartilage                  A02.165.257                    A02.165.257.250
    Elastic Cartilage              Laryngeal Cartilages           A02.165.257                    A02.165.257.625
    Laryngeal Cartilages           Arytenoid Cartilage            A02.165.257.625                A02.165.257.625.083
    Laryngeal Cartilages           Cricoid Cartilage              A02.165.257.625                A02.165.257.625.211
    Laryngeal Cartilages           Epiglottis                     A02.165.257.625                A02.165.257.625.411
    Laryngeal Cartilages           Thyroid Cartilage              A02.165.257.625                A02.165.257.625.870
    
    8 rows selected.
    
    SQL> 
    SY.
  • 2. Re: hierarchical query
    AzharHusain Journeyer
    Currently Being Moderated
    null

    Edited by: Azhar Husain on Jan 3, 2013 12:57 PM
  • 3. Re: hierarchical query
    Solomon Yakobson Guru
    Currently Being Moderated
    Actually, unless you want it ordered by level, there is no need for hierarchical query:
    select  b.term "PARENT TERM",
            a.term "CHILD TERM",
            b.relation_no "PARENT RELATION_NO",
            a.relation_no "CHILD RELATION_NO"
      from  test_1 a,
            test_1 b
      where instr(a.relation_no,'.') != 0
        and b.relation_no = substr(a.relation_no,1,instr(a.relation_no,'.',-1) - 1)
    /
    
    PARENT TERM                    CHILD TERM                     PARENT RELATION_NO             CHILD RELATION_NO
    ------------------------------ ------------------------------ ------------------------------ -------
    Musculoskeletal                Cartilage                      A02                            A02.165
    Cartilage                      Elastic Cartilage              A02.165                        A02.165.257
    Elastic Cartilage              Laryngeal Cartilages           A02.165.257                    A02.165.257.625
    Elastic Cartilage              Ear Cartilage                  A02.165.257                    A02.165.257.250
    Laryngeal Cartilages           Thyroid Cartilage              A02.165.257.625                A02.165.257.625.870
    Laryngeal Cartilages           Epiglottis                     A02.165.257.625                A02.165.257.625.411
    Laryngeal Cartilages           Cricoid Cartilage              A02.165.257.625                A02.165.257.625.211
    Laryngeal Cartilages           Arytenoid Cartilage            A02.165.257.625                A02.165.257.625.083
    
    8 rows selected.
    
    SQL> 
    SY.
  • 4. Re: hierarchical query
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Here's one way:
    SELECT       p.term     AS parent_term
    ,       c.term     AS child_term
    ,       p.relation_no     AS relation_no
    FROM       test_1  p
    JOIN       test_1  c   ON  p.relation_no = SUBSTR ( c.relation_no
                                               , 1
                                   , INSTR ( c.relation_no
                                           , '.'
                                        , -1
                                        ) - 1
                                   )
    ORDER BY  relation_no
    ;
  • 5. Re: hierarchical query
    879994 Newbie
    Currently Being Moderated
    Great. Appreciate your help.

Legend

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