Infinite Loop - What could be causing it?

ReportingAnalyst · Dec 19, 2006

I have a simple table Tree which has 6 rows of data. I want to convert this data to nested table Stack. But it seems that I am entering into an infinite loop. Any ideas what I am doing wrong...

Code:

--select member_subordinate, member_manager
--from amp_allmysubordinates_postings
--WHERE Owner_DateOfPost = (SELECT MAX(Owner_DateOfPost) FROM Amp_AllMySubordinates_POstings)

IF Exists(Select * From Information_Schema.Tables Where Table_Name = 'Tree'
and TABLE_TYPE ='BASE TABLE')
	Begin
		drop table [dbo].[Tree]
	End
GO

create table tree
(emp Char(10) Not Null,
Boss Char(10));

--Insert sample data into tree
INSERT INTO TREE Values ('Albert', NULL);
INSERT INTO TREE Values ('Bert', 'Albert');
INSERT INTO TREE Values ('Chuck', 'Albert');
INSERT INTO TREE Values ('Donna', 'Chuck');
INSERT INTO TREE Values ('Eddie', 'Chuck');
INSERT INTO TREE Values ('Fred', 'Chuck');

IF Exists(Select * From Information_Schema.Tables Where Table_Name = 'Stack'
and TABLE_TYPE ='BASE TABLE')
	BEGIN
		drop table [dbo].[Stack]
	END
GO

CREATE TABLE Stack
(Stack_top INTEGER Not Null,
employee Char(10) Not Null,
Lft Integer,
Rgt Integer);

--Select * from Tree
--Select * from Stack

BEGIN
DECLARE @lft_rgt INTEGER, @stack_pointer INTEGER, @max_lft_rgt INTEGER;

SET @max_lft_rgt = 2 * (SELECT COUNT(*) FROM Tree);

INSERT INTO Stack (Stack_top, employee, Lft, Rgt)
SELECT 1, emp, 1, @max_lft_rgt
FROM Tree
WHERE boss IS NULL;

SET @lft_rgt = 2;
SET @stack_pointer = 1;

DELETE FROM Tree
WHERE boss IS NULL;

[b]--Till here it is working fine. I can see the insert into Stack 
--for the top most manager with Stack_top=1, emp=Albert, lft=1, rgt=12(2*6 rows of the table-tree)

--I can also see the row of Albert deleted from the table Tree because his 'Boss' column has a null value
[/b]
-- The stack is ready and ready to use

WHILE (@lft_rgt < @max_lft_rgt)
Begin	
	IF EXISTS(SELECT * FROM Stack AS S1, Tree AS T1
                  WHERE S1.employee = T1.boss
                  AND S1.stack_top = @stack_pointer)
	BEGIN --push when stack_top has subordinates and set the lft value
		INSERT INTO Stack(Stack_top, employee, Lft, Rgt)
		SELECT (@stack_pointer + 1), MIN(T1.emp), @lft_rgt, NULL
		FROM Stack AS S1, Tree AS T1
		WHERE S1.employee = T1.boss
		AND S1.stack_top = @stack_pointer;
		
		--remove this row from Tree
		
		DELETE FROM Tree
		WHERE emp = (SELECT employee FROM Stack WHERE stack_top = @stack_pointer + 1);
	
		SET @stack_pointer = @stack_pointer + 1;
	END --push
	ELSE
	BEGIN --pop the stack and set the rgt value
		UPDATE Stack
		SET rgt = @lft_rgt,
		stack_top = -stack_top
		WHERE stack_top = @stack_pointer

		SET @stack_pointer = @stack_pointer - 1;
	END --pop
		
	END; -- IF
END; --While

SQLDenis · Dec 19, 2006

because you are not changing @lft_rgt inside the while loop

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

SQLBill · Dec 19, 2006

Please don't post the same question twice. You already posted this in...thread183-1314554 where I responded to it. Same answer as SQLDenis gave you.

-SQLBill

Posting advice: FAQ481-4875

ReportingAnalyst · Dec 19, 2006

Sorry about it.

I commented out the while loop and I do not see any infinite loop now.

However instead of seeing the stack table with these values

Stack
-----

Code:

Stack_top   Employee  Lft    Rgt
1           Albert    1      12
2           Bert      2      3
3           Chuck     4      11
4           Donna     5      6
5           Eddie     7      8
6           Fred      9      10

I see only 2 rows of data in the Stack table.

Code:

Stack_top   Employee  Lft    Rgt
1           Albert    1      12
2           Bert      2      <NULL>

SQLBill · Dec 19, 2006

Okay....do this in steps:

Clear out Stack and run JUST this part:

DECLARE @lft_rgt INTEGER, @stack_pointer INTEGER, @max_lft_rgt INTEGER;

SET @max_lft_rgt = 2 * (SELECT COUNT(*) FROM Tree);

INSERT INTO Stack (Stack_top, employee, Lft, Rgt)
SELECT 1, emp, 1, @max_lft_rgt
FROM Tree
WHERE boss IS NULL;

SET @lft_rgt = 2;
SET @stack_pointer = 1;

DELETE FROM Tree
WHERE boss IS NULL;

What does it return?

-SQLBill

Posting advice: FAQ481-4875

SQLBill · Dec 19, 2006

Actually, you will have to recreate TREE also, since one step deleted from that table.

-SQLBill

Posting advice: FAQ481-4875

SQLBill · Dec 19, 2006

Then run this part: (I commented out the WHILE because you said you did so).

BEGIN
DECLARE @lft_rgt INTEGER, @stack_pointer INTEGER, @max_lft_rgt INTEGER;

SET @max_lft_rgt = 2 * (SELECT COUNT(*) FROM Tree);
SET @lft_rgt = 2;
SET @stack_pointer = 1;

--WHILE (@lft_rgt < @max_lft_rgt)
Begin
IF EXISTS(SELECT * FROM Stack AS S1, Tree AS T1
WHERE S1.employee = T1.boss
AND S1.stack_top = @stack_pointer)
BEGIN --push when stack_top has subordinates and set the lft value
INSERT INTO Stack(Stack_top, employee, Lft, Rgt)
SELECT (@stack_pointer + 1), MIN(T1.emp), @lft_rgt, NULL
FROM Stack AS S1, Tree AS T1
WHERE S1.employee = T1.boss
AND S1.stack_top = @stack_pointer;

--remove this row from Tree

DELETE FROM Tree
WHERE emp = (SELECT employee FROM Stack WHERE stack_top = @stack_pointer + 1);

SET @stack_pointer = @stack_pointer + 1;
END --push
ELSE
BEGIN --pop the stack and set the rgt value
UPDATE Stack
SET rgt = @lft_rgt,
stack_top = -stack_top
WHERE stack_top = @stack_pointer

SET @stack_pointer = @stack_pointer - 1;
END --pop

END; -- IF
END; --While

I think you really need the WHILE, you just need to increment @lft_rgt.

-SQLBill

Posting advice: FAQ481-4875

ReportingAnalyst · Dec 20, 2006

I cleared the stack. Did not drop the table. To start with my 'Tree' table had these rows:

Code:

--Insert sample data into tree
INSERT INTO TREE Values ('Albert', NULL);
INSERT INTO TREE Values ('Bert', 'Albert');
INSERT INTO TREE Values ('Chuck', 'Albert');
INSERT INTO TREE Values ('Donna', 'Chuck');
INSERT INTO TREE Values ('Eddie', 'Chuck');
INSERT INTO TREE Values ('Fred', 'Chuck');

Then the Stack table is created:

Code:

CREATE TABLE Stack
(Stack_top INTEGER Not Null,
employee Char(10) Not Null,
Lft Integer,
Rgt Integer)

As requested by you I ran just this part. Now the 'Stack' table has no data.

Code:

DECLARE @lft_rgt INTEGER, @stack_pointer INTEGER, @max_lft_rgt INTEGER

SET @max_lft_rgt = 2 * (SELECT COUNT(*) FROM Tree)

INSERT INTO Stack (Stack_top, employee, Lft, Rgt)
SELECT 1, emp, 1, @max_lft_rgt
FROM Tree
WHERE boss IS NULL

SET @lft_rgt = 2
SET @stack_pointer = 1

DELETE FROM Tree
WHERE boss IS NULL

'Stack' table has now 1 row with the data:

Code:

Stack_top   Employee  Lft    Rgt
1           Albert    1      12

Now I definitely need a while loop to keep inserting numbers into the 'Stack' table. But for now, I commented out the while loop and executed this part of the code.

Code:

DECLARE @lft_rgt INTEGER, @stack_pointer INTEGER, @max_lft_rgt INTEGER;

SET @max_lft_rgt = 2 * (SELECT COUNT(*) FROM Tree);
SET @lft_rgt = 2;
SET @stack_pointer = 1;

--WHILE (@lft_rgt < @max_lft_rgt)
Begin    
    IF EXISTS(SELECT * FROM Stack AS S1, Tree AS T1
                  WHERE S1.employee = T1.boss
                  AND S1.stack_top = @stack_pointer)
    BEGIN --push when stack_top has subordinates and set the lft value
        INSERT INTO Stack(Stack_top, employee, Lft, Rgt)
        SELECT (@stack_pointer + 1), MIN(T1.emp), @lft_rgt, NULL
        FROM Stack AS S1, Tree AS T1
        WHERE S1.employee = T1.boss
        AND S1.stack_top = @stack_pointer;
        
        --remove this row from Tree
        
        DELETE FROM Tree
        WHERE emp = (SELECT employee FROM Stack WHERE stack_top = @stack_pointer + 1);
    
        SET @stack_pointer = @stack_pointer + 1;
    END --push
    ELSE
    BEGIN --pop the stack and set the rgt value
        UPDATE Stack
        SET rgt = @lft_rgt,
        stack_top = -stack_top
        WHERE stack_top = @stack_pointer

        SET @stack_pointer = @stack_pointer - 1;
    END --pop
        
    END; -- IF
--END; --While

When I execute the above code, I see the row added in the Stack table:

Code:

Stack_top   Employee  Lft    Rgt
1           Albert    1      12
2           Chuck     2      <NULL>

I do not want to see <NULL> for the 'Rgt' column, so I changed this insert statement from

Code:

INSERT INTO Stack(Stack_top, employee, Lft, Rgt)
        SELECT (@stack_pointer + 1), MIN(T1.emp), @lft_rgt, NULL
        FROM Stack AS S1, Tree AS T1
        WHERE S1.employee = T1.boss
        AND S1.stack_top = @stack_pointer;

TO

Code:

INSERT INTO Stack(Stack_top, employee, Lft, Rgt)
        SELECT (@stack_pointer + 1), MIN(T1.emp), @lft_rgt, (@stack_pointer + 1)
        FROM Stack AS S1, Tree AS T1
        WHERE S1.employee = T1.boss
        AND S1.stack_top = @stack_pointer;

Where do I increment the @lft-rgt in the code?

Thanks.

ReportingAnalyst · Dec 20, 2006

Any answers on this one?

How can I convert the Tree table to this stack table....

THanks.

SQLBill · Dec 20, 2006

Try it right here:

SET @stack_pointer = @stack_pointer + 1;
SET @lft_rgt = @lft_rgt + 1
END --push

-SQLBill

Posting advice: FAQ481-4875

ReportingAnalyst · Dec 20, 2006

Thanks for the tip SQL Bill.

My code now is:

Code:

IF Exists(Select * From Information_Schema.Tables Where Table_Name = 'Tree'
and TABLE_TYPE ='BASE TABLE')
	Begin
		drop table [dbo].[Tree]
	End
GO

create table tree
(emp Char(10) Not Null,
Boss Char(10))

--Insert sample data into tree
INSERT INTO TREE Values ('Albert', NULL)
INSERT INTO TREE Values ('Bert', 'Albert')
INSERT INTO TREE Values ('Chuck', 'Albert')
INSERT INTO TREE Values ('Donna', 'Chuck')
INSERT INTO TREE Values ('Eddie', 'Chuck')
INSERT INTO TREE Values ('Fred', 'Chuck')

IF Exists(Select * From Information_Schema.Tables Where Table_Name = 'Stack'
and TABLE_TYPE ='BASE TABLE')
	BEGIN
		drop table [dbo].[Stack]
	END
GO

CREATE TABLE Stack
(Stack_top INTEGER Not Null,
employee Char(10) Not Null,
Lft Integer,
Rgt Integer)

--Select * from Tree
--Select * from Stack

DECLARE @lft_rgt INTEGER, @max_lft_rgt INTEGER, @stack_pointer INTEGER

Set @lft_rgt = 2
Set @max_lft_rgt = 2 * (Select Count(*) FROM Tree)
Set @stack_pointer = 1

INSERT INTO Stack 
SELECT 1, emp, 1, @max_lft_rgt
FROM Tree
Where Boss is null

Delete from Tree
Where Boss is null

While @lft_rgt <= (@max_lft_rgt - 2)
begin
IF EXISTS(Select * from stack as s1, tree as t1 where s1.employee=t1.boss and s1.stack_top = @stack_pointer)
	BEGIN --push when top has subordinates, set lft value
		insert into stack
		select(@stack_pointer+1), min(t1.emp), @lft_rgt, null
		from stack as s1, tree as t1
		where s1.employee = t1.boss
		and s1.stack_top = @stack_pointer
	
		--delete the row from the tree table
		delete from tree
		where emp = (select employee from stack where stack_top = @stack_pointer + 1)
	
		set @lft_rgt = @lft_rgt + 1
		set @stack_pointer = @stack_pointer + 1
	END --push
	ELSE
	BEGIN -- pop the stack and set rgt value
		UPDATE Stack
		Set rgt = @lft_rgt, stack_top = -stack_top --pops the stack
		WHERE stack_top = @stack_pointer
		SET @lft_rgt = @lft_rgt + 1
		Set @stack_pointer = @stack_pointer -1
	end --pop
--	End --IF
End -- While

select * from tree

Select * from Stack

But there is one flaw still in the code:

Instead of seeing the table like this:

Code:

Stack_top   Employee  Lft    Rgt
1           Albert    1      12
2           Bert      2      3
3           Chuck     4      11
4           Donna     5      6
5           Eddie     7      8
6           Fred      9      10

I see the table like this:

Code:

Stack_top   Employee  Lft    Rgt
1           Albert    1      12
2           Bert      2      3
3           Chuck     4      NULL
4           Donna     5      6
5           Eddie     7      8
6           Fred      9      10

'Chuck' should have a 'rgt' value of 11 and not null. Any ideas what else I should add in the above code.

Thanks.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Infinite Loop - What could be causing it?

ReportingAnalyst

MIS

SQLDenis

Programmer

SQLBill

MIS

ReportingAnalyst

MIS

SQLBill

MIS

SQLBill

MIS

SQLBill

MIS

ReportingAnalyst

MIS

ReportingAnalyst

MIS

SQLBill

MIS

ReportingAnalyst

MIS

Similar threads

Part and Inventory Search

Sponsor