This discussion is archived
7 Replies Latest reply: Nov 20, 2012 12:42 AM by Sphenix RSS

[WTA] - Populate leveling ID

Sphenix Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    Please post the expected output and your DB version..
  • 2. Re: [WTA] - Populate leveling ID
    jeneesh Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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...

Legend

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