Author |
Topic |
sonytr
Starting Member
10 Posts |
Posted - 2007-12-06 : 15:57:40
|
Can you please help me to write a recursive query in sql server 2000?I have got a table WORKORDER. wonum and parent are the two columns in the table. A wonum can have children.Those children can have children, so on. if i am giving a wonum,it should display all the children ,their children and so on.Sample data in the table is as followswonum parent7792 NULL7793 7792165305 77937794 77927795 7792eg:7792 is a workorder,which has got children and grand children7793 is a child of 7792165305 is a child of 77937794 is a child of 7792 and7795 is a child of 7792When I give the 7792 in the query,it should fetch all the children and grand children,etc. output should be :779316530577947795How can we do that? |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-06 : 15:58:31
|
Using SQL Server 2000 or SQL Server 2005? E 12°55'05.25"N 56°04'39.16" |
|
|
sonytr
Starting Member
10 Posts |
Posted - 2007-12-06 : 16:00:03
|
it is SQL Server 2000 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-06 : 16:02:50
|
How would you like the records returned?As a comma separated string as your example? E 12°55'05.25"N 56°04'39.16" |
|
|
sonytr
Starting Member
10 Posts |
Posted - 2007-12-06 : 16:05:04
|
no...i just need to get those values as the output of that query. no need to have it as a comma separated string |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-06 : 16:35:59
|
[code]DECLARE @Sample TABLE (WoNum INT, Parent INT)INSERT @SampleSELECT 7792, NULL UNION ALLSELECT 7793, 7792 UNION ALLSELECT 165305, 7793 UNION ALLSELECT 7794, 7792 UNION ALLSELECT 7795, 7792DECLARE @Start INT, @Generation INTSELECT @Start = 7792, @Generation = 0DECLARE @Stage TABLE (Generation INT, WoNum INT)INSERT @StageSELECT 0, WoNumFROM @SampleWHERE Parent = @StartWHILE @@ROWCOUNT > 0 BEGIN SET @Generation = @Generation + 1 INSERT @Stage SELECT @Generation, s.WoNum FROM @Sample AS s INNER JOIN @Stage AS w ON w.WoNum = s.Parent WHERE w.Generation = @Generation - 1 AND NOT EXISTS (SELECT * FROM @Stage AS r WHERE r.WoNum = s.WoNum) ENDSELECT WoNumFROM @Stage[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-06 : 16:38:15
|
SQL Server 2005 wayDECLARE @Sample TABLE (WoNum INT, Parent INT)INSERT @SampleSELECT 7792, NULL UNION ALLSELECT 7793, 7792 UNION ALLSELECT 165305, 7793 UNION ALLSELECT 7794, 7792 UNION ALLSELECT 7795, 7792DECLARE @Start INTSET @Start = 7792;WITH Yak (WoNum)AS ( SELECT WoNum FROM @Sample WHERE Parent = @Start UNION ALL SELECT s.WoNum FROM @Sample AS s INNER JOIN Yak AS y ON y.WoNum = s.Parent)SELECT WoNumFROM Yak E 12°55'05.25"N 56°04'39.16" |
|
|
sonytr
Starting Member
10 Posts |
Posted - 2007-12-06 : 17:02:55
|
Peso..Thanks for your reply..can you please get me a single query for getting the output? I have to write that query to generate a report. Report designer can only take a single query..please help me |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-06 : 17:05:33
|
There are none............ E 12°55'05.25"N 56°04'39.16" |
|
|
sonytr
Starting Member
10 Posts |
Posted - 2007-12-06 : 17:07:44
|
I tried "WITH Yak (WoNum)AS ( SELECT WoNum FROM workorder WHERE Parent = '7792' UNION ALL SELECT s.WoNum FROM workorder AS s INNER JOIN Yak AS y ON y.WoNum = s.Parent)SELECT WoNumFROM Yak" It is giving an error "Server: Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'WITH'." Can we do some modification in that query to make it working? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-06 : 17:09:59
|
Read the header!That is the SQL SERVER 2005 approach. E 12°55'05.25"N 56°04'39.16" |
|
|
sonytr
Starting Member
10 Posts |
Posted - 2007-12-06 : 17:14:19
|
So i will have to use a stored procedure for this..right? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-06 : 17:22:57
|
Yes. E 12°55'05.25"N 56°04'39.16" |
|
|
sonytr
Starting Member
10 Posts |
Posted - 2007-12-06 : 17:26:35
|
one more favor..If I am giving more than one wonum as input,how to handle it? user should be able to give any number of wonum as input to see the children.. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-06 : 17:29:47
|
http://www.sommarskog.se/arrays-in-sql-2000.html E 12°55'05.25"N 56°04'39.16" |
|
|
sonytr
Starting Member
10 Posts |
Posted - 2007-12-06 : 17:33:50
|
Thanks for the link peso..So will I be able to do it for any number of input wonum? input wonums will change everytime.. Users will be giving different number of wonums at different time |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-07 : 03:09:43
|
Yes. E 12°55'05.25"N 56°04'39.16" |
|
|
|