Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Hierarchy example - Ken Henderson's book - not working Expand / Collapse
Author
Message
Posted Saturday, February 23, 2013 6:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 8:23 AM
Points: 272, Visits: 734
I’m working on the hierarchy lesson in Ken Henderson’s The Guru’s Guide to Transact-SQL. He wants to show two things. First, how to write a query that populates a temporary table (#org_chart) with all possible chartdepth levels detected in the staff table. Secondly, how to write a query that shows all the boss/subordinate relationships regardless of the levels by which they are removed from each other.

But, I am having trouble with the first part, the query for building the #org_chart table. I've written it below, verbatum from book. It is syntactically correctly but when I run it adds no subsequent rows to the #org_chart table. Will someone tell me what is missing? Run in this order, the DDL, query 1, and query 2, should produce a table with 40+ rows:

 
--DDL for creating and populating staff table

create table staff(employee int primary key, employee_name varchar(10),
supervisor int null references staff (employee))

insert staff
values
(1, 'groucho', 1),
(2, 'chico', 1),
(3, 'harpo', 2),
(4, 'zeppo', 2),
(5, 'moe', 1),
(6, 'larry', 5),
(7, 'curly', 5),
(8, 'shemp', 5),
(9, 'joe', 8),
(10, 'curly joe', 9);

--query #1
--establishes first 10 rows of table, with first level of chart depth

select chartdepth=1, employee=o2.employee, supervisor=o1.employee
into #org_chart
from staff o1 inner join staff o2 on (o1.employee=o1.supervisor)

--query #2 does not work for me
--it is supposed to populate #org_chart with remaining levels of chartdepth.
--A while loop repeats the insert as many times as necessary to process all levels in staff table

while (@@rowcount > 0)
begin
insert #org_chart (chartdepth, employee, supervisor)
select distinct o1.chartdepth+1, o2.employee, o1.supervisor
from #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)
where o1.chartdepth=(select max(chartdepth) from #org_chart)
and o1.supervisor<>o1.employee
end

--if following the execution of query #1 you run the below query about 4-5 times, it accomplishes what query #2 is supposed to be able to
--accomplish with a While loop

insert into #org_chart
select distinct o1.chartdepth+1, o2.employee, o1.supervisor
from #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)
where o1.chartdepth=(select max(chartdepth) from #org_chart)

Why is query #2 not working? Thanks.
Post #1423378
Posted Sunday, February 24, 2013 6:37 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 25, 2014 7:47 PM
Points: 388, Visits: 421
There's a problem in the SELECT statement in the WHILE. In the JOIN, there's only one record where ( o1.employee = o2.supervisor ) and it is excluded by the WHERE clause. Can you check the query in the book one more time?

					
SELECT DISTINCT
o1.chartdepth + 1 ,
o1.employee ,
o1.supervisor
FROM
#org_chart o1
JOIN
#org_chart o2
ON ( o1.employee = o2.supervisor )
WHERE
o1.chartdepth = (
SELECT
MAX(#org_chart.chartdepth)
FROM
#org_chart
)
AND
o1.supervisor <> o1.employee

Post #1423456
Posted Monday, February 25, 2013 10:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 8:23 AM
Points: 272, Visits: 734
SQL_Enthusiast,
I double and triple checked the book and the WHILE query in my original post is a facsimile.

The where clause condition is slightly different, it excludes o1.supervisor<>o1.employee. The JOIN is on o1.employee=o2.supervisor. I'm not sure if that makes a difference....
Post #1423694
Posted Monday, February 25, 2013 10:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
polkadot (2/25/2013)
SQL_Enthusiast,
I double and triple checked the book and the WHILE query in my original post is a facsimile.

The where clause condition is slightly different, it excludes o1.supervisor<>o1.employee. The JOIN is on o1.employee=o2.supervisor. I'm not sure if that makes a difference....


your "where" makes query to return 0 rows, so nothing get inserted.
What data you expect in a table as a result of the execution of query #2?



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1423702
Posted Monday, February 25, 2013 11:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:05 PM
Points: 36,716, Visits: 31,166
polkadot (2/23/2013)
I’m working on the hierarchy lesson in Ken Henderson’s The Guru’s Guide to Transact-SQL.


I'd like to recommend a different approach to hierarchies. Please see the following 2 articles. And I guarantee the code in the articles works.
http://www.sqlservercentral.com/articles/Hierarchy/94040/
http://www.sqlservercentral.com/articles/T-SQL/94570/



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1423891
Posted Sunday, March 3, 2013 1:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 8:23 AM
Points: 272, Visits: 734
OK, I'll check these links out but in the end I know I will come around to wanting to understand KH's chapter. His code the 'long way' works, but his code with the While statement doesn't and I want to know why....
But, first, I want to understand hierarchies. So I will check out links.....
....which i just did and they are for people who already have at least a basic understanding of hierarchies such as what an adjacency list is and how it's supposed to be maintained....

I came to Ken Henderson, in order to get the fundamentals in hierarchies. I am working my way through the book as a way to improve my SQL querying. It would be very helpful to have someone look at this specific example....he's a bit of a legend/icon in the SQL community. Anywone have a copy of the Guru's Guide copyright 2000? Anyone willing to take a look at that While loop and see if they know why it's not working?
Post #1426011
Posted Sunday, March 3, 2013 2:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 8:23 AM
Points: 272, Visits: 734
Reiterating problem. This works:
--DDL for creating and populating staff table

create table staff(employee int primary key, employee_name varchar(10),
supervisor int null references staff (employee))

insert staff
values
(1, 'groucho', 1),
(2, 'chico', 1),
(3, 'harpo', 2),
(4, 'zeppo', 2),
(5, 'moe', 1),
(6, 'larry', 5),
(7, 'curly', 5),
(8, 'shemp', 5),
(9, 'joe', 8),
(10, 'curly joe', 9);

--query #1
--establishes first 10 rows of table, with first level of chart depth

select chartdepth=1, employee=o2.employee, supervisor=o1.employee
into #org_chart
from staff o1 inner join staff o2 on (o1.employee=o1.supervisor)

--query#2
--following the execution of query #1 run the below query about 4-5 times (f5 x 5), #org_chart will be populated with about 5 levels of chart depth

insert into #org_chart
select distinct o1.chartdepth+1, o2.employee, o1.supervisor
from #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)
where o1.chartdepth=(select max(chartdepth) from #org_chart)

---verify population of #org_chart with this query
Select * from #org_chart;

---query #3 returns boss -> subordinate relationships
select s.employee_name, supervises='supervises', e.employee_name
from #org_chart o join staff s on (o.supervisor=s.employee)
inner join staff e on (o.employee=e.employee)
where o.supervisor<>o.employee




HOWEVER, if you want to spare yourself hitting f5 five times, you are supposed to be able to run this query instead:


while (@@rowcount > 0)
begin
insert #org_chart (chartdepth, employee, supervisor)
select distinct o1.chartdepth+1, o2.employee, o1.supervisor
from #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)
where o1.chartdepth=(select max(chartdepth) from #org_chart)
and o1.supervisor<>o1.employee
end



But this while query doesn't work. What corrections need to be made to while query to do the job?
Post #1426015
Posted Sunday, March 3, 2013 3:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 20, 2014 8:23 AM
Points: 272, Visits: 734
But this works (a subsequent lesson)....and the only difference is the SELECT seq=IDENTITY(int). "This approach uses the IDENTITY() function with SELECT...INTO to add an identity column to the work table. It then uses this column to sort the result set when returning it." (K.H. p 245)

select seq=identity(int), chartdepth=1, employee=o2.employee,
supervisor=o1.employee
into #org_chart
from staff o1 join staff o2 on (o1.employee=o2.supervisor);

while (@@rowcount > 0)
begin
insert #org_chart (chartdepth, employee, supervisor)
select distinct o1.chartdepth+1, o2.employee, o1.supervisor
from #org_chart o1 join #org_chart o2 on (o1.employee=o2.supervisor)
where o1.chartdepth=(select max(chartdepth) from #org_chart)
and o1.supervisor<>o1.employee
end

select s.employee_name, supervises='supervises', e.employee_name
from #org_chart o join staff s on (o.supervisor=s.employee)
inner join staff e on (o.employee=e.employee)
where o.supervisor<>o.employee
order by seq


Can anyone explain the reason K.H. may have shown one supposedly working example without the "SELECT seq=IDENTITY(int)" and one with?
Post #1426027
Posted Sunday, March 3, 2013 3:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:05 PM
Points: 36,716, Visits: 31,166
polkadot (2/25/2013)
SQL_Enthusiast,
I double and triple checked the book and the WHILE query in my original post is a facsimile.

The where clause condition is slightly different, it excludes o1.supervisor<>o1.employee. The JOIN is on o1.employee=o2.supervisor. I'm not sure if that makes a difference....


I didn't notice the post above before. Is the code you most recently posted an exact duplicate of Ken's code or not?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1426029
Posted Sunday, March 3, 2013 3:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:05 PM
Points: 36,716, Visits: 31,166
polkadot (3/3/2013)
Can anyone explain the reason K.H. may have shown one supposedly working example without the "SELECT seq=IDENTITY(int)" and one with?


Yes. It's easy. He made a mistake. It happens.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1426030
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse