Connect By Prior with previous values

Question

I'm sorry, I tried to find answer, but I found not. I would to have too previous(!) ARNT as parentARTNR and POSNR as parentPOSNR in my query. I tried CONNECT_BY_ROOT, but this is only TOP ARTNR and POSNR. Than I tried SYS_CONNECT_BY_PATH(t1.ARTNR, '/') as parentARTNR, but that returns all tree.

Do you know how can I do that?

This SQL works fine, but it's without parent values.

SELECT 
    level,
    t1.ARTNR, 
    t1.POSNR
FROM 
    SMSTLPOS t1
START WITH 
    t1.ARTNR = '057516'
CONNECT BY t1.ARTNR = PRIOR t1.KOMPARTNR

I would something as:

SELECT 
 level,
 t1.ARTNR, 
 t1.POSNR,
 t0.ARTNR as parentARTNR,
 t0.POSNR as parentPOSNR
FROM 
 SMSTLPOS t1
START WITH 
 t1.ARTNR = '057516'
CONNECT BY t1.ARTNR = PRIOR t1.KOMPARTNR

Or for example declare variable value with update... I have no idea.

Here is the same what I want to do for Oracle in sql server:

with
  n(KOMPARTNR, ARTIKEL_NR, level, parentARTNR, parentPOSNR) as (
    select SMSTLPOS.KOMPARTNR,
           SMSTLPOS.ARTNR,
           1 as level,
           SMSTLPOS.ARTNR as parentARTNR,
           SMSTLPOS.POSNR as parentPOSNR
      from SMSTLPOS
     WHERE SMSTLPOS.ARTNR='51269'  -- ARTNR isn't constant  

   UNION ALL

    SELECT SMSTLPOS1.KOMPARTNR,
           SMSTLPOS1.ARTNR,
           n.level + 1,
           n.parentARTNR,
           n.parentPOSNR
      from SMSTLPOS as SMSTLPOS1, n
     WHERE n.KOMPARTNR = SMSTLPOS1.ARTNR
)
SELECT * from n

Let me understand better your question... Do you want to select level, ARTNR that would be the previous node, POSN that would be the next node and the parents the first and the last node related with the current node? — warwreken, Jan 27 '16 at 16:28
I want from level in recursion: current ARNT, current POSNR, level and previous ARTN and POSNR (from level-1). For example: Level=2, ARTNR(level2), POSNR(level2), parentARTNR(level1), parentPOSNR(level1). And yes, this is oracle SQL, I have too this sql recursion for sql server(with n... union all), but there are 2 tables, SMSTLPOS as n and second SMSTLPOS1, where I have a value in selects and SMSTLPOS1.parentARTNR = n.ARTNR. In oracle in recursion is only one table.... — user3811974, Jan 28 '16 at 08:01
Here is for example my sql server recursion, this I need to do for Oracle. ------ with n(KOMPARTNR, ARTIKEL_NR, level, parentARTNR, parentPOSNR) as (select SMSTLPOS.KOMPARTNR , SMSTLPOS.ARTNR, 1 as level, SMSTLPOS.ARTNR as parentARTNR, SMSTLPOS.POSNR as parentPOSNR from SMSTLPOS WHERE SMSTLPOS.ARTNR='51269' UNION ALL SELECT SMSTLPOS1.KOMPARTNR , SMSTLPOS1.ARTNR, n.level+1, n.parentARTNR, n.parentPOSNR from SMSTLPOS as SMSTLPOS1, n WHERE n.KOMPARTNR = SMSTLPOS1.ARTNR ) SELECT * from n — user3811974, Jan 28 '16 at 08:11
I have an idea. Can I use something as "SUBSTR(SYS_CONNECT_BY_PATH(SMSTLPOS.ARTNR, '/'),2, INSTR (SYS_CONNECT_BY_PATH(SMSTLPOS.ARTNR, '/'),'/',2)-2)"? In this case it works as CONNECT_BY_ROOT, but when I change it, it should work... — user3811974, Jan 28 '16 at 09:58

user3811974 · Accepted Answer · 2016-01-28T12:41:09.580

OMG, I did it! :D All is in SYS_CONNECT_BY_PATH, but this is in format /10/20/30/40 and I wanted(from this example) 30. Firstly I used substr and get /10/20/30, then I found position of '/' and again used substr on previous substr. Is it good or do you have someone better idea?

SELECT 
 level,
 SMSTLPOS.ARTNR, 
 SMSTLPOS.POSNR,
 SUBSTR((SUBSTR(SYS_CONNECT_BY_PATH(SMSTLPOS.POSNR, '/'),0, (INSTR(SYS_CONNECT_BY_PATH(SMSTLPOS.POSNR, '/'), '/',-1)-1))), (INSTR(SUBSTR(SYS_CONNECT_BY_PATH(SMSTLPOS.POSNR, '/'),0, (INSTR(SYS_CONNECT_BY_PATH(SMSTLPOS.POSNR, '/'), '/',-1)-1)), '/', -1))+1) as parentPOSNR,
 SUBSTR((SUBSTR(SYS_CONNECT_BY_PATH(SMSTLPOS.ARTNR, '/'),0, (INSTR(SYS_CONNECT_BY_PATH(SMSTLPOS.ARTNR, '/'), '/',-1)-1))), (INSTR(SUBSTR(SYS_CONNECT_BY_PATH(SMSTLPOS.ARTNR, '/'),0, (INSTR(SYS_CONNECT_BY_PATH(SMSTLPOS.ARTNR, '/'), '/',-1)-1)), '/', -1))+1) as parentARTNR
FROM 
 SMSTLPOS
START WITH 
 SMSTLPOS.ARTNR = '057516'
CONNECT BY SMSTLPOS.ARTNR = PRIOR SMSTLPOS.KOMPARTNR

Connect By Prior with previous values

1 Answers1