Sql Query with equvalent to sys_connect_by Path in

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sql Query with equvalent to sys_connect_by Path in

Author

Topic

venkatkrishna
Starting Member

12 Posts

Posted - 2007-05-21 : 01:04:43

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

Hello,

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. i could not able to find these in the Sql Server, can any one help me out from this situvation.

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

Regards,
Krishna.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-21 : 03:54:44

Duplicate of http://sqlteam.com/forums/topic.asp?TOPIC_ID=83859
Also refer this
http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources