Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Recursive Query

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 follows

wonum parent
7792 NULL
7793 7792
165305 7793
7794 7792
7795 7792

eg:
7792 is a workorder,which has got children and grand children

7793 is a child of 7792
165305 is a child of 7793
7794 is a child of 7792 and
7795 is a child of 7792

When I give the 7792 in the query,it should fetch all the children and grand children,etc.
output should be :
7793
165305
7794
7795

How 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"
Go to Top of Page

sonytr
Starting Member

10 Posts

Posted - 2007-12-06 : 16:00:03
it is SQL Server 2000
Go to Top of Page

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"
Go to Top of Page

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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 16:35:59
[code]DECLARE @Sample TABLE (WoNum INT, Parent INT)

INSERT @Sample
SELECT 7792, NULL UNION ALL
SELECT 7793, 7792 UNION ALL
SELECT 165305, 7793 UNION ALL
SELECT 7794, 7792 UNION ALL
SELECT 7795, 7792

DECLARE @Start INT,
@Generation INT

SELECT @Start = 7792,
@Generation = 0

DECLARE @Stage TABLE (Generation INT, WoNum INT)

INSERT @Stage
SELECT 0,
WoNum
FROM @Sample
WHERE Parent = @Start

WHILE @@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)
END

SELECT WoNum
FROM @Stage[/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 16:38:15
SQL Server 2005 way
DECLARE	@Sample TABLE (WoNum INT, Parent INT)

INSERT @Sample
SELECT 7792, NULL UNION ALL
SELECT 7793, 7792 UNION ALL
SELECT 165305, 7793 UNION ALL
SELECT 7794, 7792 UNION ALL
SELECT 7795, 7792

DECLARE @Start INT

SET @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 WoNum
FROM Yak



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

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
Go to Top of Page

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"
Go to Top of Page

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 WoNum
FROM Yak"

It is giving an error "Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'."

Can we do some modification in that query to make it working?
Go to Top of Page

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"
Go to Top of Page

sonytr
Starting Member

10 Posts

Posted - 2007-12-06 : 17:14:19
So i will have to use a stored procedure for this..right?
Go to Top of Page

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"
Go to Top of Page

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..
Go to Top of Page

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"
Go to Top of Page

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
Go to Top of Page

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"
Go to Top of Page
   

- Advertisement -