I'm trying to use the below query into HPALM.
However this is a Oracle syntax and I need to translate it to SQL Server.
Indeed, SYS_CONNECT_BY_PATH is an Oracle feature.
SELECT ls_father_id,ls_id,substr(SYS_CONNECT_BY_PATH(ls_name,'\'),2) "Path"
FROM lists
START WITH ls_name in (select ls_name from lists where ls_father_id = 0)
CONNECT BY PRIOR ls_id=ls_father_id
It is it possible to translate it? If yes, how to do?
Thank you
You can use recursive CTE for this (in Oracle also), so you can check it first in your current Oracle database.
with a (ls_father_id, ls_id, path_) as (
select
ls_father_id
, ls_id
, ls_name as path_
from lists
where ls_name in (select f.ls_name from lists f where f.ls_father_id = 0)
union all
select
b.ls_father_id
, b.ls_id
, a.path_ || '\' || b.ls_name
from b
join a
on b.ls_father_id = a.ls_id
)
select *
from a
Example below
create table t
as
with a (ls_id, ls_father_id, ls_name) as (
select 1, 0 , 'aaa' from dual union all
select 2, 1 , 'bb' from dual union all
select 3, 0 , 'c' from dual union all
select 4, 2 , 'cca' from dual union all
select 5, 4 , 'vvv' from dual union all
select 6, 3 , 'f' from dual union all
select 7, 1 , 'ee' from dual
)
select *
from a
select
t.*
, substr(sys_connect_by_path(ls_name, '\'), 2) as path_
from t
start with ls_name in (select f.ls_name from t f where f.ls_father_id = 0)
connect by prior ls_id = ls_father_id
order by ls_id
LS_ID | LS_FATHER_ID | LS_NAME | PATH_
----: | -----------: | :------ | :-------------
1 | 0 | aaa | aaa
2 | 1 | bb | aaa\bb
3 | 0 | c | c
4 | 2 | cca | aaa\bb\cca
5 | 4 | vvv | aaa\bb\cca\vvv
6 | 3 | f | c\f
7 | 1 | ee | aaa\ee
with a (ls_id, ls_father_id, path_) as (
select
t.ls_id
, t.ls_father_id
, cast(ls_name as varchar(1000))
from t
where ls_name in (select f.ls_name from t f where f.ls_father_id = 0)
union all
select
t.ls_id, t.ls_father_id, a.path_ || '\' || t.ls_name
from a
join t
on a.ls_id = t.ls_father_id
)
select *
from a
order by ls_id
LS_ID | LS_FATHER_ID | PATH_
----: | -----------: | :-------------
1 | 0 | aaa
2 | 1 | aaa\bb
3 | 0 | c
4 | 2 | aaa\bb\cca
5 | 4 | aaa\bb\cca\vvv
6 | 3 | c\f
7 | 1 | aaa\ee
db<>fiddle here
