sql - Connect By Prior with previous values -

i'm sorry, tried find answer, found not. have previous(!) arnt parentartnr , posnr parentposnr in query. tried connect_by_root, top artnr , posnr. tried sys_connect_by_path(t1.artnr, '/') parentartnr, returns tree.

do know how can that?

this sql works fine, it's without parent values.

select      level,     t1.artnr,      t1.posnr      smstlpos t1 start      t1.artnr = '057516' connect t1.artnr = prior t1.kompartnr 

i as:

select   level,  t1.artnr,   t1.posnr,  t0.artnr parentartnr,  t0.posnr parentposnr   smstlpos t1 start   t1.artnr = '057516' connect t1.artnr = prior t1.kompartnr 

or example declare variable value update... have no idea.

here same want oracle in sql server:

with   n(kompartnr, artikel_nr, level, parentartnr, parentposnr) (     select smstlpos.kompartnr,            smstlpos.artnr,            1 level,            smstlpos.artnr parentartnr,            smstlpos.posnr parentposnr       smstlpos      smstlpos.artnr='51269'  -- artnr isn't constant       union      select smstlpos1.kompartnr,            smstlpos1.artnr,            n.level + 1,            n.parentartnr,            n.parentposnr       smstlpos smstlpos1, n      n.kompartnr = smstlpos1.artnr ) select * n  

omg, did it! :d in sys_connect_by_path, in format /10/20/30/40 , wanted(from example) 30. firstly used substr , /10/20/30, found position of '/' , again used substr on previous substr. or have 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) 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) parentartnr   smstlpos start   smstlpos.artnr = '057516' connect smstlpos.artnr = prior smstlpos.kompartnr