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).
CODE
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
--here is where I get the 1st Invalid column name 'emp'.
IF EXISTS(SELECT * FROM Stack AS S1, Tree AS T1
WHERE S1.emp = T1.boss
AND S1.stack_top = @.stack_pointer)
BEGIN --push when stack_top has subordinates and set the lft value
--2nd invalid column error 'emp'
INSERT INTO Stack
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
So:
select * from Stack S1, Tree T1 where S1.emp = T1.boss........|||
I don′t think that the AS is the problem. Make sure that the tables are really created with the specified columns. Make sure that you have a look at the right owner / schema, not working on an old schema. I addition you should always specifiy the column where you want to insert the values to make the code more readable for others:
INSERT INTO TableA
(
COLA,
COLB
)
(...)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Jens K. Suessmeyer wrote:
I don′t think that the AS is the problem. Make sure that the tables are really created with the specified columns. Make sure that you have a look at the right owner / schema, not working on an old schema. I addition you should always specifiy the column where you want to insert the values to make the code more readable for others:
INSERT INTO TableA
(
COLA,
COLB
)
(...)HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
It must be a valid table, unless we're not getting all of the error specifics. The first part of his stored proc code, he inserts into the stack table...
Would it be better to setup a cursor instead of using the "IF EXISTS" syntax?|||
I do not get any errors now. I made the changes to my insert statements by specifying the columns. I am following the example of Nested Sets as mentioned by Joe Celko's SQL Trees. His example is in this link: http://www.I am trying with the simple data structure as mentioned here. When I run it now, it is still running as I am typing this message. If I know what is wrong, then I shall know where to look for my errors. I do not have any other table by these names. I double checked in the Enterprise Manager.
This is what I had typed in the Query Analyzer and tried to run it.
-
--How can I check if the table Tree exists. Only if it exists, then I would like to drop the table Tree.
DROP TABLE Tree;
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');
DROP Table 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));
--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, emp, 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;
-- 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.emp = 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, emp, Lft, Rgt)
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
--
Any ideas?
Thanks. PS: I noticed that the execution took 10 minutes and 27 seconds and I had to stop it. (Isn't that too long for a table which has only 6 rows of data)
|||IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Tables WHERE Table_name = 'Tree') --Not specifying the owner / schema, you should be sure to alway use the same owner / schema name
DROP TABLE Tree
You are missing something as you are not changing the break condition
WHILE (@.lft_rgt < @.max_lft_rgt)
Therefore it will run forever in an infinite loop.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||I am still getting an infinite loop. Where do I increment the @.lft_rgt as you mentioned that @.lft_rgt will be always less than @.max_lft_rgt.
I just copied the code of Joe Celko's SQL Trees and wanted to try it on my SQL Server before applying it to my solution. But apparently I was misled that is would be error-free :(
Any ideas what I should do to convert the table Tree to Stack?
Thanks.
Tree Table:
Emp Boss
Albert NULL
Bert Albert
Chuck Albert
Donna Chuck
Eddie Chuck
Fred Chuck
Stack Table:
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
My code now is:
[code]
IF Exists(Select * From SYSOBJECTS Where Name = 'Tree')
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 SYSOBJECTS Where Name = 'Tree')
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
--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
-- The stack is ready and ready to use
--WHILE (@.lft_rgt < @.max_lft_rgt)
--Begin
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
--DECLARE @.stack_pointer integer, @.lft_rgt integer
--SET @.stack_pointer = 1
--SET @.lft_rgt = 2
INSERT INTO Stack(Stack_top, employee, Lft, Rgt)
SELECT (@.stack_pointer + 1), MIN(T1.emp), @.lft_rgt, @.stack_pointer + 2
FROM Stack AS S1, Tree AS T1
WHERE S1.employee = T1.boss
AND S1.stack_top = @.stack_pointer;
--increment the @.lft_rgt
--SET @.lft_rgt = @.lft_rgt + 1
--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 + 1,
stack_top = -stack_top
WHERE stack_top = @.stack_pointer
SET @.stack_pointer = @.stack_pointer - 1;
END --pop
END; -- IF
END; --While
|||You are not changing the condition in the loop, that why it is infinite. Increase the @.lft_rgt or decrease the @.max_lft_rgt.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Ok. I changed the code to increment the @.lft_rgt and I do not see an infinite loop.
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, NULL
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
Instead of seeing my table as:
employee lft rgt
Albert 1 12
Bert 2 3
Chuck 4 11
Donna 5 6
Eddie 7 8
Fred 9 10
However, I see my result table Stack as:
Stack_top Employee Lft Rgt
1 Albert 1 NULL
-2 Bert 2 3
2 Chuck 4 NULL
-3 Donna 5 6
-3 Eddie 7 8
-3 Fred 9 10
What can I do to make 'chuck' have a value of 11 and 'Albert' have a value of 12?
Thanks.
|||
Ok, I was inserting value of Null for albert:
INSERT INTO Stack
SELECT 1, emp, 1, null
FROM Tree
Where Boss is null
I changed the query to:
INSERT INTO Stack
SELECT 1, emp, 1, @.max_lft_rgt
FROM Tree
Where Boss is null
However what can I do to change the rgt value for 'Chuck' to 11 and not null?
Any ideas?
Thanks.
