Trees and Hierarchy - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Trees and Hierarchy

Author

Topic

vavs
Starting Member

24 Posts

Posted - 2010-09-09 : 11:17:28

I have been trying to replicate the code that Rob Volk presented back in 2002. I am trying to set up the adjacency model. I run into an error and cannot seem to get past it.

I have a table FRINFM that has all of my inventory. I am only selecting a few key fields for this exercise.
TAG# char(15)
OrigTag# char(15)
Gauge char(10)
GRADE char(5)
Weight int(4)

This is the original table

I am then inserting all of the columns plus two to a new table Lineage

Here is the code:
/*
select TAG#,OrigTag#,Gauge,GRADE,Weight,CAST(0 as tinyint)as Depth,CAST(0 as varchar(255))as Lineage
into Lineage
from FRINFM
*/
This works great

Now I check for the base level

/*
update Lineage
SET Lineage = TAG# + '/'
where TAG#=OrigTag#
*/

This also works fine. Rob just uses the '/' for the set Lineage.

Now here is my problem query:

/*
update L set L.depth = P.depth + 1,
L.Lineage = P.Lineage + L.TAG# + '/'
from Lineage as L
inner join Lineage as P on (L.TAG# = P.OrigTag#)
where P.Depth>=0
and P.Lineage <> 0
and L.Depth = 0
*/

when I run this I get

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '00999206 01/' to a column of data type int.

I have tried to set the data type for the P.Lineage = varchar(255) but it did not work. Can anyone help me.

The next step in the process is to loop through the process and get all of the grandchildren etc in the file.

TIA

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-09 : 11:28:14

This should fix it:

update L set L.depth = P.depth + 1,
L.Lineage = P.Lineage + L.TAG# + '/'
from Lineage as L
inner join Lineage as P on (L.TAG# = P.OrigTag#)
where P.Depth>=0
and P.Lineage <> '0'
and L.Depth = 0

If you're using SQL 2008, I'd recommend using the hierarchyid data type to represent the lineage, it's specifically designed and optimized for this sort of thing.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-09 : 13:28:20

Why do you want to destroy First Normal Form (1NF) and use procedural code with this adjacency list model? This is so easy to do in a Nested Sets model.

Most of the clients I have moved from adjacency list models to Nested Sets have found that their data had orphans and cycles, so be prepared to do a lot of data scrubbing and report corrections.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources