February 23, 2013 at 6:16 pm
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.
--Quote me
February 24, 2013 at 6:37 pm
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
February 25, 2013 at 10:21 am
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....
--Quote me
February 25, 2013 at 10:47 am
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?
February 25, 2013 at 11:38 pm
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
Change is inevitable... Change for the better is not.
March 3, 2013 at 1:54 pm
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?
--Quote me
March 3, 2013 at 2:15 pm
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?
--Quote me
March 3, 2013 at 3:41 pm
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?
--Quote me
March 3, 2013 at 3:49 pm
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
Change is inevitable... Change for the better is not.
March 3, 2013 at 3:52 pm
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
Change is inevitable... Change for the better is not.
March 3, 2013 at 4:04 pm
Well, if that's all it is, then great. I tend to overblame myself. I was sure it was me.
Well, Joe Celko forewarded the book. I would have thought he would have caught it.
--Quote me
March 3, 2013 at 4:23 pm
polkadot (3/3/2013)
Well, if that's all it is, then great. I tend to overblame myself. I was sure it was me.Well, Joe Celko forewarded the book. I would have thought he would have caught it.
Just because he wrote a forward doesn't mean he did a tech review of the book.
March 3, 2013 at 4:38 pm
polkadot (3/3/2013)
Well, if that's all it is, then great. I tend to overblame myself. I was sure it was me.Well, Joe Celko forewarded the book. I would have thought he would have caught it.
Nah, not to worry... Overblaming yourself is actually a good thing. Those that do the opposite tend to fall in the arrogant category and that's when code really begins to suffer.
I ran the code you provided and it's really odd to me. I cannot, for the life of me, figure out why Ken wanted to create six copies of the same hierarchy with all people at all levels reporting to the same supervisor.
I agree with Lynn, as well. Just because someone forwards a book, doesn't mean they even looked at the content never mind actually doing any technical reviews. Even some authors don't check their own code and not all technical reviewers are good technical reviewers.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2013 at 4:40 pm
levity.....I tried.:laugh: He's given me a hard time before
Thank you Jeff!!!
I cannot, for the life of me, figure out why Ken wanted to create six copies of the same hierarchy with all people at all levels reporting to the same supervisor.
I thought it was to help the reader understand multiple layers of chartdepth. After a few iterations you exhaust all the chartdepths possible and can run a query to extract all boss->subordinate relationships. Groucho is the head boss, but there are a few minor bosses as well. It's not perfectly linear. I thought that was a good approach, before launching into the While loop....to demonstrate what the while loop does. That part was actually good for me.
--Quote me
March 3, 2013 at 5:25 pm
Maybe, but I'd have done it a different way. The original data already has more than 1 level.
The original data also has a flaw in it that makes it impossible to enforce the rule that no one should report to themselves to prevent cycles in code. Groucho's superviser ID should have been NULL. A NULL would not interfere with the reference that a superviser ID must also be an employee ID.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply