June 18, 2008 at 6:01 am
Is their any way to create an Hierarchal Query with out using CTE.
June 19, 2008 at 7:06 am
If by heirarchal you mean being able to use recursion, I'm pretty sure the answer is no. On the other hand, let me provide an example of what COULD be referred to as heirarchal...
DECLARE @A_ROCK int, @A_HARD_PLACE int
SET @A_ROCK = 2345
SET @A_HARD_PLACE = 45678
SELECT A, B, C
FROM (
SELECT A, B
FROM dbo.mydb1
WHERE B NOT IN ( SELECT B FROM dbo.yourdatabase) AND
A IN ( SELECT A FROM dbo.theirdatabase)
) AS DB1 LEFT OUTER JOIN (
SELECT C
FROM dbo.yourdb1
WHERE C IN ( SELECT C FROM dbo.ourdatabase) AND
C BETWEEN @A_ROCK AND @A_HARD_PLACE
) AS DB2
ON DB1.A LIKE '%'+DB2.C+'%'
WHERE DB1.A BETWEEN @A_ROCK AND @A_HARD_PLACE AND
DB2.C NOT IN (SELECT A FROM dbo.mydb2)
Not sure if I'm helping here, so maybe it would be best if you defined EXACTLY what you mean by heirarchal.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 19, 2008 at 7:39 am
Yes, as it has been done before SQL Server supported the hierarchical CTE (WITH) syntax.
If however, your looking for the equivalent of Oracle's robust CONNECT BY, you're out of luck. It also appears that the ANSI committee has decided that recursive CTE (WITH) is the way to perform hierarchical queries. It is implemented in IBM's DB2 as the hierarchical query method.
That said, I'm still using the UDF that I wrote for SQL Server 2000 to "walk" my hierarchical trees. It has good performance and I can order (sort) the intermediate nodes properly (e.g., on name vs. the ID of the node) without having to perform some kludgy concatenation. See the following post: http://www.sqlservercentral.com/Forums/Topic487077-338-3.aspx#bm488803
July 3, 2008 at 7:38 am
Yes, You are correct by using recurssion only
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply