February 5, 2009 at 11:10 pm
Hello,
Is there in T-SQL an analog of Oracle's CONNECT BY extention?
If not, can you suggest how can I achieve node linking in T-SQL
Thanks,
Pit
February 6, 2009 at 7:27 am
In simple terms, there isn't any equivalent to CONNECT BY of Oracle, but you can achieve the same functionality using CTE (Common Table Expressions)...
Here is an AdventureWorks example from BOOKS ONLINE...
USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
GO
--Ramesh
February 6, 2009 at 7:42 am
I think you can achieve the functionality more easily with the HierarchyID datatype in SQL 2008. Check out these articles, see if they have what you need:
http://msdn.microsoft.com/en-us/library/bb677290.aspx
http://msdn.microsoft.com/en-us/library/bb677193.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 6, 2009 at 8:30 am
Hi
Simulating "CONNECT BY" in SQL Server.
http://www.sqlteam.com/article/more-trees-hierarchies-in-sql
Thanks
Vijaya Kadiyala
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply