April 18, 2008 at 7:01 am
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
When you select from the CTE, you can use any of the usual structures for select statements. You can use aggregates (sum, count, et al), you can use joins, you can use Where clauses, Group By, Having, and Order By.
Just treat the CTE the same way you would any other derived table.
For example:
;with HierarchyCTE (Lvl, ID, ParentID, Name) as
(select 1, ID, ParentID, Name
from dbo.Hierarchy
union all
select Lvl + 1, h2.ID, h2.ParentID, h2.Name
from dbo.Hierarchy h2
inner join HierarchyCTE
on h2.ParentID = HierarchyCTE.ID)
select Lvl, ID, ParentID, Name
from HierarchyCTE
order by Name
- 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
April 18, 2008 at 7:05 am
Rabia Mansour (3/18/2007)
Thanks for the article.
My questions is : Suppose we need to relate one row data to two parents. By doing that I get only one instance of that data, not both.
I've added :
It is not reasnable in this example but it is reasnable in other.
How it should be done to accomplish this need.
It's just a question of building the join in the part of the CTE after the Union All.
For example:
;with FTreeCTE (Generation, ID, Parent1ID, Parent2ID, Name) as
(select 1, ID, Parent1ID, Parent2ID, Name
from dbo.FamilyTree
union all
select Generation + 1, ft2.ID, ft2.Parent1ID, ft2.Parent2ID, ft2.Name
from dbo.FamilyTree ft2
inner join FTreeCTE
on ft2.Parent1ID = FTreeCTE.ID
or ft2.Parent2ID = FTreeCTE.ID)
select Generation, ID, Parent1ID, Parent2ID, Name
from FTreeCTE
order by Generation
- 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
April 18, 2008 at 7:07 am
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.
- 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
April 18, 2008 at 7:08 am
Good article, by the way.
- 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
April 18, 2008 at 10:39 am
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
April 18, 2008 at 1:04 pm
Mike:
If I'm understanding you correctly, this should duplicate your many-to-many table (functionally):
create table HierarchyComplex (
ID int not null,
ParentID int not null,
constraint PK_HC primary key (id, parentid))
go
insert into dbo.hierarchycomplex (id, parentid)
select 1, 0 union all
select 2, 1 union all
select 3, 1 union all
select 2, 3 union all
select 4, 0 union all
select 5, 4
go
;with CTE (ID, ParentID) as
(select ID, null
from dbo.hierarchycomplex
where id = 1
union all
select h2.id, h2.parentid
from dbo.hierarchycomplex h2
inner join cte
on h2.parentid = cte.id)
select *
from cte
You'll see that ID 2 (presumably an FK to another table), is a child of ID 1, and a child of ID 3. That should parallel your "same branch appears multiple time". Right?
The above CTE pulls the expected data from this. I get ID 2 twice, once under ID 1, once under ID 3.
If I add ID 6 as a child of ID 2, then 6 also shows up in the results twice, once per instance of ID 2.
Does that make sense?
- 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
April 18, 2008 at 1:16 pm
Any Ideas on how to incorporate into historical views?
April 18, 2008 at 1:16 pm
GSquared,
Thanks! I'll give that a try in one of the databases I'm working with and let you know.
Mike
April 18, 2008 at 2:50 pm
john.racer (4/18/2008)
Any Ideas on how to incorporate into historical views?
Not sure what you mean. What are you looking at doing with the historical view?
- 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
April 21, 2008 at 1:00 am
Hi,
I tried out the example to create a recursive query(Sample 1)...i get the following error when I run the script :
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
I am using SQL server 2005. Have I missed out on something?
Thanks,
Dev
April 21, 2008 at 5:13 am
hi
i used the given example, but i got error saying
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
i am using sql server 2000, I don't know in sql server 2000 CTE is possible or not
April 21, 2008 at 6:17 am
shamshudheen (4/21/2008)
hii used the given example, but i got error saying
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
i am using sql server 2000, I don't know in sql server 2000 CTE is possible or not
It is not possible in SQL Server 2000. In SQL Server 2000, this is achieved by loops traversing all the levels under the node. SQL 2005 provides the new feature Common Table Expression (CTE), which can be used to solve this request.
Read this: http://www.sqlservercentral.com/articles/T-SQL/2926/
April 21, 2008 at 6:58 am
itsme_dev08 (4/21/2008)
Hi,I tried out the example to create a recursive query(Sample 1)...i get the following error when I run the script :
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
I am using SQL server 2005. Have I missed out on something?
Thanks,
Dev
That usually means you need to add a semicolon, ";", before the "with".
- 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
April 21, 2008 at 11:01 am
The basic business requirement is to roll sales up from employee to lead to manager to director. We have much movement in our sales organization. We want to historically calculate sale mad in history and roll up as it was at that point in time.
Performing this with CTE gets tricky here as you know have people potentially in multiple places in the hierarchy for given efective dates.
We use CTE for current state very successfully but have only been able to use it running one day at a time in history to roll up successfully.
Ideas?
April 21, 2008 at 1:25 pm
john.racer (4/21/2008)
The basic business requirement is to roll sales up from employee to lead to manager to director. We have much movement in our sales organization. We want to historically calculate sale mad in history and roll up as it was at that point in time.Performing this with CTE gets tricky here as you know have people potentially in multiple places in the hierarchy for given efective dates.
We use CTE for current state very successfully but have only been able to use it running one day at a time in history to roll up successfully.
Ideas?
I'm assuming you have history data in your database that says, for example, "Joe Salesman was under Bob Manager between 1 Jan 2008 and 29 Feb 2008; Joe Salesman was under Sally Manager between 1 March and null". "Null" indicating here that the end date is unknown because the data is still current.
If so, the recursive part of the CTE needs to include the time range. Then, when you join that to the sales table, you include the time range in the join.
Am I understanding the situation correctly?
- 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
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply