Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Recursive Queries in SQL Server 2005 Expand / Collapse
Author
Message
Posted Friday, August 15, 2008 5:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 16, 2009 6:59 AM
Points: 2, Visits: 11
Satish Jha (5/24/2006)
Thanks for this article. I have one question here -how can I sort the result from CTE in hierarchical order as well as siblings on some orther order say all records of one level by firstname. In the article the example only sorts it ib hierarchical order


I have used recursion before using while loop and found a tree code very useful. The tree code allows you to sort the data as if it was displayed on a hierarchical tree view. I used another new CTE function to return incremental identifiers for a portion of data (ie, incremental identifiers for the child row of a certain parent) and staggered them in varchar of length 40 (allowing 20 levels with a max of 99 children).

The formatting was a little tricky. I used the RIGHT and LEFT Functions to pad the numbers with 0s
and I have assumed that you want the ordering of the members of each leave to be by the field DateJoined.

For the anchor member definition I had some similar code for that field...

right('0' + cast(ROW_NUMBER() OVER (Partition BY a.ReportingLevelID ORDER BY a.DateJoined) as varchar) ,2) + REPLICATE('00' ,19)

and for the recursive member definition I used...

left(left(b.TreeCode, (b.TreeLevel+1)*2) + right('0' + cast(ROW_NUMBER() OVER (Partition BY b.TreeLevel ORDER BY a.DateJoined) as varchar) ,2) + REPLICATE('00' ,19),40)
Post #553345
Posted Friday, August 15, 2008 5:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 16, 2009 6:59 AM
Points: 2, Visits: 11
MikeAngelastro (4/18/2008)
GSquared (4/18/2008)
MikeAngelastro (3/13/2008)
I was able to solve the many-to-many situation typically found in a product tree by using a recursive cursor in a stored procedure continuously adding to a temp table until the tree is exhausted. It's pretty fast. I just prefer the CTE.

Am I wrong in assuming that a CTE cannot handle this situation?


A CTE can definitely handle this situation. Test one, it will almost certainly out-perform the cursor.


GSquared,

Thanks for your input.

I did use a CTE initially. But situations arrived later where the resulting record set had too many rows. My tests indicated that the extra rows appear as soon as a branch appears more once in the table; that is, any branch can be a child in more than one product tree. The product-tree I am dealing with has this possibility and therefore I have to handle it. I searched the internet for a sample CTE that was specifically designed to handle this condition and found none.

Because it turned out that the column values in the extra rows appeared to be identical to one of the original rows, I tried to use a “DISTINCT” qualifier but the CTE refused to run, even when I used the following approach:

SELECT DISTINCT FROM CTE

where “CTE” is the CTE’s record set result - extra rows and all.

And even here, when rows have the same values as other rows, it does not necessarily mean they should be excluded from the result; this would happen if the same branch appears more than one in the same overall product tree. Given these results, how can the CTE be constructed in order to exclude the extra rows?

Thanks,

Mike


I am having a similar problem. I originally used a while loop to create the heirachical structure and if a child had been examined before I would restrict the itteration so that the geneology of that child was only shown once.

I used the following code to stop the recursion. (@ChildLotIDs was the heirachical temporary table and consumedlotid was the and the ConsumedLotID was the field that allowed the self join.)

C.ConsumedLotID NOT IN (SELECT EL.ConsumedLotID FROM @ChildLotIDs EL WHERE ClI.ParameterLotID = EL.ParameterLotID AND EL.TreeLevel <= @Level)

However when using CTE I can't reference the table twice in the recursive member defintion..

I get the following error..

Recursive member of a common table expression 'LotGeneology' has multiple recursive references.

Any ideas how to get around this ?
Post #553352
Posted Friday, February 6, 2009 2:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:10 PM
Points: 30, Visits: 223
Thanks a lot Shrinivas for that.

I just want to make a Point is that to get same sort of output in the Oracle is quite easier...

Just use START WITH clause specify who would be the Parent and Oracle takes care of all. I do agree that SS 2005 is better but Oracle is still the Best.

Hary Pank


Thanks a lot,
Hary
Post #651453
Posted Friday, February 6, 2009 2:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:10 PM
Points: 30, Visits: 223
Thanks a lot Shrinivas for that.

I just want to make Point is that to get results in the Oracle it quite a lot Easier...

Just use START WITH clause specify who would be the Parent and Oracle takes care of all. I do agree that SS 2005 has better but Oracle is still the Best.

Hary Pank


Thanks a lot,
Hary
Post #651458
Posted Saturday, February 14, 2009 12:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 9, 2012 7:25 AM
Points: 2, Visits: 2
Very good article. However, I couldn't implement this without having an existing Level value. I kept working at this and came up with what appears to be a nice alternative. It's not a recursive procedure but gave me the results I wanted to I thought I'd share my work.

My main table structure is a single table with related entities I'll call departments, where Departments are related to one another.

DepartmentID int,
ParentID int,
Title varchar(25)

What I needed was a procedure that I could pass an ID into and it would give me that ID and all related IDs down the tree. I did this using a temporary table. First inserting the Main record that was passed into the procedure then retrieving all child records using a join back to the temp table. This required using a loop which I did by using a WHILE statement and checking the @@ROWCOUNT var. This looks to work fine for retrieving segments of the table but won't work for getting the entire table, which is simple enough to do with a select statement. Here's the rest.

CREATE PROCEDURE [dbo].[store_GetProductByDepartment]
(
@DepartmentID int
)
AS

-- create temp table for top 2 levels of menu
CREATE TABLE #TEMP_TABLE(
[DepartmentID] [int] NOT NULL,
[ParentID] [int] NULL,
[Title] [nvarchar](256) NULL
)

-- get the first record
INSERT INTO #TEMP_TABLE (DepartmentID, ParentID, Title)
SELECT
d1.DepartmentID
, d1.ParentID
, d1.Title
FROM dbo.store_Department d1
WHERE d1.DepartmentID = @DepartmentID

-- loop through the records and insert found departments
WHILE @@ROWCOUNT > 0
BEGIN
INSERT INTO #TEMP_TABLE (DepartmentID, ParentID, Title)
SELECT
d1.DepartmentID
, d1.ParentID
, d1.Title
FROM dbo.store_Department d1
INNER JOIN #TEMP_TABLE d2 ON d1.ParentID = d2.DepartmentID
WHERE d1.DepartmentID NOT IN (SELECT DepartmentID FROM #TEMP_TABLE)
END

-- testing
-- select * from #TEMP_TABLE

SELECT *
FROM store_Product
WHERE
store_Product.DepartmentID in (SELECT DepartmentID FROM #TEMP_TABLE )

I haven't tested this in all situations. If anyone can see problems from doing this or have any suggestions for improvement please post. Hope this helps.

Post #657220
Posted Thursday, September 3, 2009 8:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:53 PM
Points: 432, Visits: 627
Hi,

In this article, I need to know what is the purpose of SortKey in CTE.

In the hierarchy, will the Top level row is returned first??
(In our project we need only the top row - so it is important that the row returned is correct)


Please let me know.

Thanks,
KB

Post #782214
Posted Thursday, September 3, 2009 8:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
KB (9/3/2009)
Hi,

In this article, I need to know what is the purpose of SortKey in CTE.

In the hierarchy, will the Top level row is returned first??
(In our project we need only the top row - so it is important that the row returned is correct)


Please let me know.

Thanks,
KB



Please dont cross post

http://www.sqlservercentral.com/Forums/Topic782210-338-1.aspx




Clear Sky SQL
My Blog
Kent user group
Post #782226
Posted Thursday, September 3, 2009 8:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:53 PM
Points: 432, Visits: 627
Dave Ballantyne (9/3/2009)
KB (9/3/2009)
Hi,

In this article, I need to know what is the purpose of SortKey in CTE.

In the hierarchy, will the Top level row is returned first??
(In our project we need only the top row - so it is important that the row returned is correct)


Please let me know.

Thanks,
KB




Please dont cross post

http://www.sqlservercentral.com/Forums/Topic782210-338-1.aspx


Sorry for that...
I didn't know whether this Discussion is active or not, so I posted here too..

Post #782237
Posted Friday, October 9, 2009 11:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:07 PM
Points: 5, Visits: 354
Thank you Srinivas. A very lucid, intelligible presentation of CTEs. I Much appreciate your efforts
Post #800965
Posted Thursday, December 30, 2010 12:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:06 AM
Points: 36, Visits: 43
Hello, I "Google'd" across this as I was pondering a solution to this problem and think it solves it nicely but it is limited to only 100 recursions. I wonder what some other solutions to the problem might be?

Problem
TSQL Challenge 46 - Remove leading occurrences of the first character in a string

[url=http://beyondrelational.com/blogs/tc/archive/2010/12/27/tsql-challenge-46-Remove-leading-occurrences-of-the-first-character-in-a-string.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2010Dec28][/url]
Post #1041135
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse