Start and Connect By Clause - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Start and Connect By Clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-03 : 08:05:14

Rahul writes "I want to convert following code from oracle to SQL Server 2005.
How should i do it?

CREATE OR REPLACE VIEW VRESOURCES AS
SELECT r.res_id,
r.last_name,
r.first_name,
r.middle_initial,
r.email,
r.is_disabled,
r.Cost_Center_Id,
r.Location_Cd,
r.org_id,
rp.mgr_res_id,
rp.mgr_last_name,
rp.mgr_first_name,
rp.mgr_middle_initial,
rp.mgr_is_disabled,
rp.mgr_org_id,
l.description Location,
rp.NamePath,
rp.FullNamePath,
rp.IDPath
FROM TRES r LEFT JOIN
( SELECT
tr.res_id,
p.res_id mgr_res_id,
p.last_name mgr_last_name,
p.first_name mgr_first_name,
p.middle_initial mgr_middle_initial,
p.is_disabled mgr_is_disabled,
p.org_id mgr_org_id,
SYS_CONNECT_BY_PATH(tr.last_name, '/') NamePath,
SYS_CONNECT_BY_PATH(tr.res_id, '/') IDPath,
SYS_CONNECT_BY_PATH(tr.last_name || ', ' ||tr.first_name, '/') FullNamePath
FROM tres tr
LEFT JOIN TRES_RES rr ON
tr.res_id=rr.res_id
LEFT JOIN TRES p ON
rr.parent_id = p.res_id
WHERE rr.assoc_type_cd='RESMGR'
START WITH rr.parent_id='ROOT'
CONNECT BY PRIOR rr.res_id=rr.parent_id) rp
ON r.res_id = rp.res_id
LEFT JOIN TLocation L
ON r.location_cd = L.Location_Cd
WHERE r.res_id <> 'ROOT'"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-03 : 09:01:00

Have a look at common table expressions.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources