Invalid Column Name Error

[ReportingAnalyst]

Hi,

Can anybody point me why I get the error
"Invalid Column Name - emp" error.

Thanks.

Table structures are:

Tree

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

Stack

CREATE TABLE Stack
(Stack_top INTEGER Not Null,
emp Char(10) Not Null,
Lft Integer NOT NULL UNIQUE CHECK (Lft > 0),
Rgt Integer NOT NULL UNIQUE CHECK (Rgt > 1));

I am trying to convert the Tree (Adjancency Model) to Stack(Nested Model).

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

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

INSERT INTO Stack
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;

-- The stack is ready and ready to use

WHILE (@lft_rgt < @max_lft_rgt)
Begin	
      [b]  --here is where I get the 1st invalid column error[/b]
	[b]IF EXISTS(SELECT * FROM Stack AS S1, Tree AS T1[/b]
                  WHERE S1.emp = T1.boss
                  AND S1.stack_top = @stack_pointer)
	BEGIN --push when stack_top has subordinates and set the lft value
                [b]--2nd invalid column error[/b]
		[b]INSERT INTO Stack[/b]
		SELECT (@stack_pointer + 1), MIN(T1.emp), @lft_rgt, NULL
		FROM Stack AS S1, Tree AS T1
		WHERE S1.emp = T1.boss
		AND S1.stack_top = @stack_pointer;
		
		--remove this row from Tree
		
		DELETE FROM Tree
		WHERE emp = (SELECT emp 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

 

[SQLBill]

When you join tables, you usually can't use the SELECT *. In your case, you definitely can't use it as you have a column EMP in both tables. How is it supposed to know which is which? You need to do:

SELECT t1.Emp, t1.Boss, s1.Emp, s1.lft, s1.rgt, s1.stack_top

or whatever order you want them in.

-SQLBill

Posting advice: FAQ481-4875

 

[ReportingAnalyst]

I changed the name of the column for the table Stack. I do not see any errors now. But it seems that I am doing something that causing an infinite loop. What could it be?

My code here is:

--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)[/b]

--I can also see the row of Albert deleted from the table Tree because his 'Boss' column has a null value

-- 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

 

[SQLBill]

First, get rid of all the semi-colons. That's an Oracle thing and not needed in SQL Server.

Next.........you have this line...

WHILE (@lft_rgt < @max_lft_rgt)

But I don't see anywhere that you increment @lft_rgt. So it will always be less than @max_lft_rgt and the script will continue forever.

-SQLBill

Posting advice: FAQ481-4875