--------------------------------------------------------------------------------

Hell,

I have some view created on my Oracle database. Same thing i want to convert to SQL Server. here are the some key words used in the oracle like
sys_connect_by_path, SUBSTR,INSTR etc. could any one help me in converting the same.

CREATE DisplayResults AS
SELECT f.acd, f.adesc, f.rcd, f.rdesc,
f.tid, f.roc, f.ros,
f.tc, f.ts, f.rc,
fe.eid, fe.pn, fe.fn, fe.ln,
fe.vm, fe.iia,
f.lct -

(case
when f.tspcnt > 1
then 1
else 0
end) lct,
u1, u2,uy, u1 + u2 +uy total
FROM (SELECT t.acd, t.adesc, t.rcd, t.rdesc,
t.tid, t.roc, t.ros,t.tc, t.ts,t.rc,
COUNT (DISTINCT
(case when h.tsr <> 'KKM'
then h.lid
else 'KKM'
end)) lct,
count (DISTINCT
(case
when h.tsr <> 'KKM'
then 'NON_KKM'
else 'KKM'
end)) tspcnt,
COUNT (CASE
WHEN h.tcd = 'UT'
THEN 1
END) u1,
COUNT (CASE
WHEN h.tcd = 'UY'
THEN 1
END) u2,
COUNT (CASE
WHEN h.tcd = 'UX'
THEN 1
END)uy
FROM Table1 t,
(SELECT cc, tcd, lid, t.rc tsr,

SUBSTR
(sys_connect_by_path (t.tid,
','),
2,
INSTR
(sys_connect_by_path
(t.tid,
','
),
',',
1,
2
)
- 2
) rtid,
CASE
WHEN LEVEL = 2
THEN tid
ELSE SUBSTR
(sys_connect_by_path
(t.tid,
','
),
INSTR
(sys_connect_by_path
(t.tid,
','
),
',',
1,
2
)
+ 1,
INSTR
(sys_connect_by_path
(t.tid,
','
),
',',
1,
3
)
- INSTR
(sys_connect_by_path
(t.tid,
','
),
',',
1,
2
)
- 1
)
END dtid
FROM Table1 t
WHERE cc = 'IO'
AND t.afg = 'X'
AND t.ttcd = 'TT'
START WITH rc = 'PP'
CONNECT BY PRIOR tid = t.rlt) h
WHERE t.cc = h.cc
AND t.rc != 'JJ'
AND t.tid IN (rtid, dtid)
GROUP BY t.acd, t.adesc,t.rcd,t.rdesc,t.tid,t.roc,t.ros,t.tc,t.ts, t.rc) f
left join (SELECT * FROM Table2 fe1
WHERE fe1.fact = 'Y') fe
ON f.tid = fe.tid
ORDER BY acd, rcd