very urgent! CONNECT BY PRIOR!!

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

SQL Server 2000 Forums

SQL Server Development (2000)

very urgent! CONNECT BY PRIOR!!

Author

Topic

akwok
Starting Member

10 Posts

Posted - 2006-10-19 : 02:08:11

Hi,

I need help to do some thing in MS SQL.
something equivalent to CONNECT BY PRIOR IN ORACLE

The columns are:
ID, PARENT_ID, MONEY
1 0 5
2 1 3
3 1 1
4 2 3
5 4 2

I want to make a count of MONEY that the whole tree of 2 has
it will be money of 2 + money of 4 + money of 5

How do we write such script
If i want to avoid using store procedures, is there anyways to do it without?
Thank you in advance!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-19 : 02:59:37

Yes there is, but only if you know the meximum allowed depth for the tree.

DECLARE @Tree TABLE (ID INT, PARENT_ID INT, [MONEY] INT)

INSERT	@Tree
SELECT	1, 0, 5 UNION ALL
SELECT	2, 1, 3 UNION ALL
SELECT	3, 1, 1 UNION ALL
SELECT	4, 2, 3 UNION ALL
SELECT	5, 4, 2


DECLARE	@Node INT

SELECT @Node = 2

SELECT		SUM(CASE
			WHEN pID = @Node THEN ISNULL(pMoney, 0) + ISNULL(c1_Money, 0) + ISNULL(c2_Money, 0) + ISNULL(c3_Money, 0)
			WHEN c1_ID = @Node THEN ISNULL(c1_Money, 0) + ISNULL(c2_Money, 0) + ISNULL(c3_Money, 0)
			WHEN c2_ID = @Node THEN ISNULL(c2_Money, 0) + ISNULL(c3_Money, 0)
			WHEN c3_ID = @Node THEN ISNULL(c3_Money, 0)
			ELSE 0
		END)
FROM		(
			SELECT		p.id pID,
					p.[Money] pMoney,
					c1.id c1_ID,
					c1.[Money] c1_Money,
					c2.id c2_ID,
					c2.[Money] c2_Money,
					c3.id c3_ID,
					c3.[Money] c3_Money
			FROM		(
						SELECT	ID,
							[Money]
						FROM	@Tree
						WHERE	Parent_ID = 0
					) p
			LEFT JOIN	@Tree c1 ON c1.Parent_ID = p.ID
			LEFT JOIN	@Tree c2 ON c2.Parent_ID = c1.ID
			LEFT JOIN	@Tree c3 ON c3.Parent_ID = c2.ID
		) x

Peter Larsson
Helsingborg, Sweden

akwok
Starting Member

10 Posts

Posted - 2006-10-19 : 03:21:30

What is the way to do when I find the level ?or with stored procedures?

because i shouldn't know how many levels i have

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-19 : 03:25:12

Make a stored procedure.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-19 : 03:33:49

Something like this.

DECLARE @Tree TABLE (ID INT, PARENT_ID INT, [MONEY] INT)

INSERT	@Tree
SELECT	1, 0, 5 UNION ALL
SELECT	2, 1, 3 UNION ALL
SELECT	3, 1, 1 UNION ALL
SELECT	4, 2, 3 UNION ALL
SELECT	5, 4, 2

DECLARE	@Iter TABLE (Generation INT, ID INT, [Money] INT)

INSERT	@Iter
SELECT	0,
	t.ID,
	t.[Money]
FROM	@Tree t
WHERE	t.ID = 2

WHILE @@ROWCOUNT > 0
	INSERT	@Iter
	SELECT	1 + (SELECT MAX(x.Generation) FROM @Iter x),
		t.ID,
		t.[Money]
	FROM	@Tree t
	WHERE	t.Parent_ID IN (SELECT s.ID FROM @Iter s WHERE s.Generation = (SELECT MAX(q.Generation) FROM @Iter q))

SELECT	SUM([Money])
FROM	@Iter

Peter Larsson
Helsingborg, Sweden

akwok
Starting Member

10 Posts

Posted - 2006-10-19 : 04:58:33

thanks for the reply.
What If i want to add a new column, and insert the sum of money of your tree for every record in the table.
as result, 1 will have 14, 2 will have 8 , 3 will have 1, 4 will have5 and 5 will have 2.

Thanks again for the quick reply.

akwok
Starting Member

10 Posts

Posted - 2006-10-19 : 05:01:42

I know that there's a method to first find out on which level each record is. then do something with the level... and add up all the money. it's like a recursive thing.

i have done it once in the past, but lost the code. sigh...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-19 : 05:07:14

How hard can it be?
When you are given a suggestion or solution for your problem, take same time and try to understand what is going on in the code.
For your latest problem, I just changed the LAST select statement as


SELECT	SUM([Money])
FROM	@Iter

SELECT		i.ID,
		i.[Money],
		(SELECT SUM(z.[Money]) FROM @Iter z WHERE z.Generation >= i.Generation) AllMoney
FROM		@Iter i
ORDER BY	i.Generation

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-19 : 05:13:38

Also change

INSERT	@Iter
SELECT	0,
	t.ID,
	t.[Money]
FROM	@Tree t
WHERE	t.ID = 2

WHERE	t.ID = 1

Even better, make this a STORED PROCEDURE and make the number after the equal sign as a parameter to the SP.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-19 : 05:24:33

[code]CREATE PROCEDURE dbo.uspGetMoneyTree
(
@TopNode INT,
@AllNodes BIT = 1
)
AS

SET NOCOUNT ON

DECLARE @Iter TABLE (Generation INT, ID INT, [Money] INT)

INSERT @Iter
SELECT 0,
t.ID,
t.[Money]
FROM MyTreeTable t
WHERE t.ID = @TopNode

WHILE @@ROWCOUNT > 0
INSERT @Iter
SELECT 1 + (SELECT MAX(x.Generation) FROM @Iter x),
t.ID,
t.[Money]
FROM @Tree t
WHERE t.Parent_ID IN (SELECT s.ID FROM @Iter s WHERE s.Generation = (SELECT MAX(q.Generation) FROM @Iter q))

SELECT i.ID,
i.[Money],
(SELECT SUM(z.[Money]) FROM @Iter z WHERE z.Generation >= i.Generation) AllMoney
FROM @Iter i
WHERE i.ID = @TopNode
OR @AllNodes = 1
ORDER BY i.Generation[/code]Call with either

uspGetMoneyTree 2
uspGetMoneyTree 1, 0
uspGetMoneyTree 2, 1

Peter Larsson
Helsingborg, Sweden

akwok
Starting Member

10 Posts

Posted - 2006-10-19 : 05:49:57

something i don't understand with your method is that, it's like a pyramid, if you are in the 2nd generation, even a record is not reporting to you, you still add his money to his account.

take record 3 for example. we shouldn't count the money of 4 and 5 to him as 4 and 5 are not reporting to him, but reporting to 2 and 4.

But really. i couldn't ask more, thanks for your help.!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-19 : 05:57:51

You don't. I just tried uspMoneyBack 3 and got this result

ID Money AllMoney
-- ----- --------
 3     1        1

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources