7 Replies Latest reply: Nov 20, 2012 2:42 AM by Sphenix RSS

    [WTA] - Populate leveling ID

    Sphenix
      I having information ID and parent ID, but any idea how to populate level id such as 1, 1.1, 1.1.1?



      CREATE TABLE "DBO"."JC_TEST_99"
      (     "PATH" VARCHAR2(4000 BYTE),
           "SORT_ID" NUMBER,
           "LEVEL_ID" NUMBER,
           "ID" VARCHAR2(20 BYTE),
           "PRNT_ID" VARCHAR2(20 BYTE),
           "NM" VARCHAR2(500 BYTE)
      ) SEGMENT CREATION IMMEDIATE
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "DM_TBS" ;
      REM INSERTING into DBO.JC_TEST_99
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /1',2,1,'1','0','Overdue');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2',3,1,'2','0','LE 1 Year');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/14',15,2,'14','2','GT 1 Month To 3 Months');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/15',16,2,'15','2','GT 3 Month To 6 Months');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/16',17,2,'16','2','GT 3 Months To 6 Months');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/17',18,2,'17','2','GT 6 Months To 1 Year');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/17/18',19,3,'18','17','GT 6 Months To 9 Months');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/17/19',20,3,'19','17','GT 9 Months To 12 Months');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/3',4,2,'3','2','On Call');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/4',5,2,'4','2','LE 1 Month');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/4/5',6,3,'5','4','LE 1 Week');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/4/5/6',7,4,'6','5','LE 3 Days');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/4/5/6/7',8,5,'7','6','Overnight');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/4/8',9,3,'8','4','GT Overnight To 1 Week');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/4/9',10,3,'9','4','GT 3 Days To 1 Month');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/4/9/10',11,4,'10','9','GT 1 Week To 1 Month');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/4/9/10/11',12,5,'11','10','GT 1 Week To 2 Weeks');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/4/9/10/12',13,5,'12','10','GT 2 Weeks To 3 Weeks');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /2/4/9/10/13',14,5,'13','10','GT 3 Weeks To 4 Weeks');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /20',21,1,'20','0','GT 1 Year');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /20/21',22,2,'21','20','GT 1 Year To 3 Years');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /20/22',23,2,'22','20','GT 3 Years To 4 Years');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /20/23',24,2,'23','20','GT 4 Years To 5 Years');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /20/24',25,2,'24','20','GT 5 Years');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /20/24/25',26,3,'25','24','GT 5 Years To 10 Years');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /20/24/26',27,3,'26','24','GT 10 Years To 15 Years');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /20/24/27',28,3,'27','24','GT 15 Years To 20 Years');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /20/24/28',29,3,'28','24','GT 20 Years');
      Insert into DBO.JC_TEST_99 (PATH,SORT_ID,LEVEL_ID,ID,PRNT_ID,NM) values (' /29',30,1,'29','0','GT 6 Months To 12 Months');
        • 1. Re: [WTA] - Populate leveling ID
          jeneesh
          Please post the expected output and your DB version..
          • 2. Re: [WTA] - Populate leveling ID
            jeneesh
            A wild guess..(In our netweork, your link is blocked)
            select path,
                   case level when 1 then '1'
                              when 2 then '1.1'
                              when 3 then '1.1.1'
                              when 4 then '1.1.1.1'
                              when 5 then '1.1.1.1.1'
                   end lvl
            from JC_TEST_99 a
            start with level_id=1
            connect by prior id = prnt_id 
            ;
            
            PATH                           LVL     
            ------------------------------ ---------
             /1                            1         
             /2                            1         
             /2/14                         1.1       
             /2/15                         1.1       
             /2/16                         1.1       
             /2/17                         1.1       
             /2/17/18                      1.1.1     
             /2/17/19                      1.1.1     
             /2/3                          1.1       
             /2/4                          1.1       
             /2/4/5                        1.1.1     
             /2/4/5/6                      1.1.1.1   
             /2/4/5/6/7                    1.1.1.1.1 
             /2/4/8                        1.1.1     
             /2/4/9                        1.1.1     
             /2/4/9/10                     1.1.1.1   
             /2/4/9/10/11                  1.1.1.1.1 
             /2/4/9/10/12                  1.1.1.1.1 
             /2/4/9/10/13                  1.1.1.1.1 
             /20                           1         
             /20/21                        1.1       
             /20/22                        1.1       
             /20/23                        1.1       
             /20/24                        1.1       
             /20/24/25                     1.1.1     
             /20/24/26                     1.1.1     
             /20/24/27                     1.1.1     
             /20/24/28                     1.1.1     
             /29                           1         
            
             29 rows selected 
            • 3. Re: [WTA] - Populate leveling ID
              Nicosa-Oracle
              Hi,

              You might wanna read that [url https://forums.oracle.com/forums/thread.jspa?threadID=1083765]thread.
              • 4. Re: [WTA] - Populate leveling ID
                Solomon Yakobson
                jeneesh wrote:
                A wild guess
                All that CASE statement is nothing more but SYS_CONNECT_BY_PATH:
                select  path,
                        ltrim(sys_connect_by_path('1','.'),'.') lvl
                  from  jc_test_99
                  start with level_id = 1
                  connect by prior id = prnt_id
                /
                
                PATH                          LVL
                ----------------------------- ----------
                 /1                           1
                 /2                           1
                 /2/14                        1.1
                 /2/15                        1.1
                 /2/16                        1.1
                 /2/17                        1.1
                 /2/17/18                     1.1.1
                 /2/17/19                     1.1.1
                 /2/3                         1.1
                 /2/4                         1.1
                 /2/4/5                       1.1.1
                
                PATH                          LVL
                ----------------------------- ----------
                 /2/4/5/6                     1.1.1.1
                 /2/4/5/6/7                   1.1.1.1.1
                 /2/4/8                       1.1.1
                 /2/4/9                       1.1.1
                 /2/4/9/10                    1.1.1.1
                 /2/4/9/10/11                 1.1.1.1.1
                 /2/4/9/10/12                 1.1.1.1.1
                 /2/4/9/10/13                 1.1.1.1.1
                 /20                          1
                 /20/21                       1.1
                 /20/22                       1.1
                
                PATH                          LVL
                ----------------------------- ----------
                 /20/23                       1.1
                 /20/24                       1.1
                 /20/24/25                    1.1.1
                 /20/24/26                    1.1.1
                 /20/24/27                    1.1.1
                 /20/24/28                    1.1.1
                 /29                          1
                
                29 rows selected.
                
                SQL> 
                SY.
                • 5. Re: [WTA] - Populate leveling ID
                  Solomon Yakobson
                  Nicosa wrote:
                  You might wanna read that [url https://forums.oracle.com/forums/thread.jspa?threadID=1083765]thread.
                  It looks like that thread doesn't provide a generic solution (without limiting level). Here is a generic solution:
                  with t as (
                             select  path,
                                     ltrim(sys_connect_by_path('1','.'),'.') lvl_path,
                                     level lvl,
                                     rownum rn
                               from  jc_test_99
                               start with level_id = 1
                               connect by prior id = prnt_id
                            )
                  select  path,
                          lvl_path
                    from  t
                    model
                      dimension by(rn)
                      measures(path,lvl_path,lvl)
                      rules(
                            lvl_path[rn > 1] order by rn = regexp_substr(lvl_path[cv() - 1] || '.','^(\d+\.){'|| (lvl[cv()] - 1) || '}') ||
                                                           (nvl(regexp_substr(lvl_path[cv() - 1],'[^.]+',1,lvl[cv()]),0) + 1)
                           )
                    order by rn
                  / 
                  
                  PATH                          LVL_PATH
                  ----------------------------- ---------------
                   /1                           1
                   /2                           2
                   /2/14                        2.1
                   /2/15                        2.2
                   /2/16                        2.3
                   /2/17                        2.4
                   /2/17/18                     2.4.1
                   /2/17/19                     2.4.2
                   /2/3                         2.5
                   /2/4                         2.6
                   /2/4/5                       2.6.1
                  
                  PATH                          LVL_PATH
                  ----------------------------- ---------------
                   /2/4/5/6                     2.6.1.1
                   /2/4/5/6/7                   2.6.1.1.1
                   /2/4/8                       2.6.2
                   /2/4/9                       2.6.3
                   /2/4/9/10                    2.6.3.1
                   /2/4/9/10/11                 2.6.3.1.1
                   /2/4/9/10/12                 2.6.3.1.2
                   /2/4/9/10/13                 2.6.3.1.3
                   /20                          3
                   /20/21                       3.1
                   /20/22                       3.2
                  
                  PATH                          LVL_PATH
                  ----------------------------- ---------------
                   /20/23                       3.3
                   /20/24                       3.4
                   /20/24/25                    3.4.1
                   /20/24/26                    3.4.2
                   /20/24/27                    3.4.3
                   /20/24/28                    3.4.4
                   /29                          4
                  
                  29 rows selected.
                  
                  SQL> 
                  SY.
                  • 6. Re: [WTA] - Populate leveling ID
                    jeneesh
                    Solomon Yakobson wrote:
                    jeneesh wrote:
                    A wild guess
                    All that CASE statement is nothing more but SYS_CONNECT_BY_PATH:
                    Yea..

                    Today is not my day.. :(
                    • 7. Re: [WTA] - Populate leveling ID
                      Sphenix
                      SUperb solomon, it is what I looking for..... and learn new thing, (model)...... need some times to digest the coding now.....

                      thanks again everyone who contributed ......thanks thanks...