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 

Answer 1

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