5 Replies Latest reply: Jan 3, 2013 3:00 PM by Solomon Yakobson RSS

    hierarchical query

    879994
      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
          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
            Azhar Husain
            null

            Edited by: Azhar Husain on Jan 3, 2013 12:57 PM
            • 3. Re: hierarchical query
              Solomon Yakobson
              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
                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
                  Great. Appreciate your help.