SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Recursive Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sonytr
Starting Member

India
10 Posts

Posted - 12/06/2007 :  15:57:40  Show Profile  Reply with Quote
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?

Edited by - sonytr on 12/06/2007 16:23:56

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 12/06/2007 :  15:58:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
10 Posts

Posted - 12/06/2007 :  16:00:03  Show Profile  Reply with Quote
it is SQL Server 2000
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 12/06/2007 :  16:02:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
10 Posts

Posted - 12/06/2007 :  16:05:04  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 12/06/2007 :  16:35:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



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

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 12/06/2007 :  16:38:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
10 Posts

Posted - 12/06/2007 :  17:02:55  Show Profile  Reply with Quote
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

Edited by - sonytr on 12/06/2007 17:05:28
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 12/06/2007 :  17:05:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
There are none............



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

sonytr
Starting Member

India
10 Posts

Posted - 12/06/2007 :  17:07:44  Show Profile  Reply with Quote
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?

Edited by - sonytr on 12/06/2007 17:10:09
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 12/06/2007 :  17:09:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
10 Posts

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 12/06/2007 :  17:22:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes.



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

sonytr
Starting Member

India
10 Posts

Posted - 12/06/2007 :  17:26:35  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 12/06/2007 :  17:29:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
10 Posts

Posted - 12/06/2007 :  17:33:50  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 12/07/2007 :  03:09:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000