2 Replies Latest reply: Jun 6, 2014 2:15 PM by msb RSS

    Hierarchy structure

    msb

      Hi All,

       

      I have a table which has the hierarcy records and want to give type of the hierarchy.

      Some more details about the table.

      for example we have a chain A->B->C->D->E which means A is replaced by B and B is replaced by C and C is replaced D and D is replaced by E. So this holds the hierachy for each element.see sample data below.

      SR_NOOLD_SR_NONEW_SR_NO
      CAB
      CBC
      CCD
      CDE
      DAB
      DBC
      DCD
      DDE
      EAB
      EBC
      ECD
      EDE

       

      If you see the above table, the old_sr_no and new_sr_no are same but it repeats for each element within the chain.

      sample table creation:

      {code}

      CREATE TABLE BL_SAMPLE_SS_DATA 

      AS 

      SELECT 'C' AS SR_NO,

             'A' AS OLD_SR_NO, 

             'B' AS NEW_SR_NO 

        FROM DUAL 

      UNION 

      SELECT 'C' AS SR_NO,

             'B' AS OLD_SR_NO, 

             'C' AS NEW_SR_NO 

        FROM DUAL

      UNION 

      SELECT 'C' AS SR_NO,

             'C' AS OLD_SR_NO, 

             'D' AS NEW_SR_NO 

        FROM DUAL 

      UNION 

      SELECT 'C' AS SR_NO,

             'D' AS OLD_SR_NO, 

             'E' AS NEW_SR_NO 

        FROM DUAL

      UNION

      SELECT 'D' AS SR_NO,

             'A' AS OLD_SR_NO, 

             'B' AS NEW_SR_NO 

        FROM DUAL 

      UNION 

      SELECT 'D' AS SR_NO,

             'B' AS OLD_SR_NO, 

             'C' AS NEW_SR_NO 

        FROM DUAL

      UNION 

      SELECT 'D' AS SR_NO,

             'C' AS OLD_SR_NO, 

             'D' AS NEW_SR_NO 

        FROM DUAL 

      UNION 

      SELECT 'D' AS SR_NO,

             'D' AS OLD_SR_NO, 

             'E' AS NEW_SR_NO 

        FROM DUAL

      UNION

      SELECT 'E' AS SR_NO,

             'A' AS OLD_SR_NO, 

             'B' AS NEW_SR_NO 

        FROM DUAL 

      UNION 

      SELECT 'E' AS SR_NO,

             'B' AS OLD_SR_NO, 

             'C' AS NEW_SR_NO 

        FROM DUAL

      UNION 

      SELECT 'E' AS SR_NO,

             'C' AS OLD_SR_NO, 

             'D' AS NEW_SR_NO 

        FROM DUAL 

      UNION 

      SELECT 'E' AS SR_NO,

             'D' AS OLD_SR_NO, 

             'E' AS NEW_SR_NO 

        FROM DUAL;

      {code}

       

      Anyone please guide me how to do the below task. see the expected output table below.

      SR_NOOLD_SR_NONEW_SR_NOTYPESEQ_NO
      CABHISTORY1
      CBCHISTORY2
      CCDSUPERCEDED3
      CDESUPERCEDED4
      DABHISTORY1
      DBCHISTORY2
      DCDHISTORY3
      DDESUPERCEDED4
      EABHISTORY1
      EBCHISTORY2
      ECDHISTORY3
      EDEHISTORY4

       

      Details:

      if you take sr_no "C", the seq_no 1 and 2 are history but seq_no 3 and 4 are superceded becuase C is superced (replaced) by d and D is replaced by E but but A and B are just history info for C.

       

      The same way for others D and E

       

      Some one could please help out on this.

        • 1. Re: Hierarchy structure
          Frank Kulash

          Hi,

           

          So, the rows are connect in a linked list; that sounds like a job for CONNECT BY, like this:

           

          SELECT  sr_no

          ,       old_sr_no

          ,       new_sr_no

          ,       SYS_CONNECT_BY_PATH (old_sr_no, '/')   AS path   -- For debugging only

          ,       CASE

                      WHEN  SYS_CONNECT_BY_PATH (old_sr_no, '/') || '/'

                      LIKE  '%/' || sr_no || '/%'

                      THEN  'SUPERCEDED'

                      ELSE  'HISTORY'

                  END                                    AS row_type

          ,       LEVEL                                  AS seq_no

          FROM    bl_sample_ss_data

          START WITH  old_sr_no  NOT IN (

                                            SELECT  new_sr_no

                                            FROM    bl_sample_ss_data

                                            WHERE   new_sr_no  IS NOT NULL    -- If necessary

                                        )

          CONNECT BY   old_sr_no  = PRIOR new_sr_no

                  AND  sr_no      = PRIOR sr_no

          ;

          You don't need to include the column I called path; I included that only to help you understand how this works.

           

          This assumes you know of something that will never be an sr_no, or even a sub-string of an old_sr_no.  I used '/' in the query above, but if your old_sr_nos can contain '/'s, then you can use '~', or '~/?' or anything else that can't occur in old_sr_no.

           

          Output:

          S O N PATH                 ROW_TYPE       SEQ_NO

          - - - -------------------- ---------- ----------

          C A B /A                   HISTORY             1

          C B C /A/B                 HISTORY             2

          C C D /A/B/C               SUPERCEDED          3

          C D E /A/B/C/D             SUPERCEDED          4

          D A B /A                   HISTORY             1

          D B C /A/B                 HISTORY             2

          D C D /A/B/C               HISTORY             3

          D D E /A/B/C/D             SUPERCEDED          4

          E A B /A                   HISTORY             1

          E B C /A/B                 HISTORY             2

          E C D /A/B/C               HISTORY             3

          E D E /A/B/C/D             HISTORY             4

          Thanks for posting the CREATE TABLE statement with the sample data; that's very helpful!

          • 2. Re: Hierarchy structure
            msb

            Thanks a lot Frank...it works