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 Sunday, March 03, 2013 4:04 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
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.
Post #1426033
Posted Sunday, March 03, 2013 4:23 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:16 PM
Points: 22,504, Visits: 30,213
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1426036
Posted Sunday, March 03, 2013 4: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 @ 3:34 PM
Points: 35,964, Visits: 30,257
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
"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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1426037
Posted Sunday, March 03, 2013 4:40 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
levity.....I tried. 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.
Post #1426038
Posted Sunday, March 03, 2013 5:25 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 35,964, Visits: 30,257
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
"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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1426049
Posted Monday, March 04, 2013 10:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
Like this? (All the same queries, but modified with Null. This leaves Groucho out altogether).
create table staff(employee int primary key, employee_name varchar(10),
supervisor int null references staff (employee))

insert staff
values
(1, 'groucho', Null),
(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);

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

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)
and o1.supervisor is not null

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
Post #1426595
Posted Wednesday, March 06, 2013 7:11 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 35,964, Visits: 30,257
Ah...my apologies. I lost track of this thread. On my way to work. I'll try to post an example tonight.

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

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1427373
Posted Wednesday, March 06, 2013 8:30 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 35,964, Visits: 30,257
Ok. I'm totally confused as to what Ken was trying to demonstrate. Let's take his first query from your last post.

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

The result of that query is the same as the following and requires no join at all because all that it is ultimately doing is returning the contents of the original table with an extra column added.

 SELECT ChartDepth = 1,
Employee,
Supervisor
FROM dbo.Staff
;

The second "paragraph" of code is clever but it produces a very nasty Cartesian Product (full Cross Join). I don't know if that would resolve itself out to a simple hash join when the table get's larger but, if it doesn't, this code would take a millenia to run on what some folks consider to be a "small" million node hierarchy that frequents the world of MLMs and some parts lists.

The third "paragraph" of code is, again, clever, but I'm not sure that you'd ever want to display something like that for a larger hierarchy and so I don't understand the overall reason for demonstrating this method.

Getting back to (literally) the root of what I was talking about (Groucho needs to have a NULL for supervisor), the original code isn't easily modified to allow for such a thing. Again, although it's clever code, I'm not sure why anyone would want to display the output of this code.

I guess the question at this point is, what would you actually like to do with this hierarchy example? Something like this? Although I don't particularly like the column names or the table name, I kept them for comparison.

 CREATE TABLE dbo.Staff
(
Employee INT NOT NULL,
Employee_Name VARCHAR(10) NOT NULL,
Supervisor INT NULL
CONSTRAINT PK_Staff
PRIMARY KEY CLUSTERED (Employee ASC),
CONSTRAINT FK_Staff_ManagerMustBeAnEmployee
FOREIGN KEY (Supervisor)
REFERENCES dbo.Staff (Employee),
CONSTRAINT CK_Staff_ManagerCannotBeSelf
CHECK (Supervisor<>Employee)
)
;
INSERT INTO dbo.Staff
(Employee,Employee_Name,Supervisor)
SELECT 1, 'Groucho' , NULL UNION ALL --1 UNION ALL
SELECT 2, 'Chico' , 1 UNION ALL
SELECT 3, 'Harpo' , 2 UNION ALL
SELECT 4, 'Zeppo' , 2 UNION ALL
SELECT 5, 'Moe' , 1 UNION ALL
SELECT 6, 'Larry' , 5 UNION ALL
SELECT 7, 'Curly' , 5 UNION ALL
SELECT 8, 'Shemp' , 5 UNION ALL
SELECT 9, 'Joe' , 8 UNION ALL
SELECT 10, 'Curly Joe' , 9
;
WITH
cteDirectReports AS
( --=== This gets the top-level supervisors(s).
-- This would be the "primer" for a loop.
SELECT Employee, Supervisor, Employee_Name, EmployeeLevel = 1,
HierarchicalPath = CAST('\'+CAST(Employee AS VARCHAR(10)) AS VARCHAR(4000))
FROM dbo.Staff
WHERE Supervisor IS NULL
UNION ALL
--==== This gets a level at a time using recursion of the CTE.
-- This would be the "body" of a loop.
SELECT e.Employee, e.Supervisor, e.Employee_Name, EmployeeLevel = d.EmployeeLevel + 1,
HierarchicalPath = CAST(d.HierarchicalPath + '\'+CAST(e.Employee AS VARCHAR(10)) AS VARCHAR(4000))
FROM dbo.Staff e
INNER JOIN cteDirectReports d ON e.Supervisor = d.Employee
) --=== This simply formats for display. I wouldn't do this in real life, either.
SELECT Employee,
Supervisor,
Employee_Name = SPACE((EmployeeLevel-1)*4) + Employee_Name,
EmployeeLevel,
HierarchicalPath
FROM cteDirectReports
ORDER BY HierarchicalPath
;


That produces the following indented name result...
Employee    Supervisor	Employee_Name	            EmployeeLevel   HierarchicalPath
1 NULL Groucho 1 \1
2 1 Chico 2 \1\2
3 2 Harpo 3 \1\2\3
4 2 Zeppo 3 \1\2\4
5 1 Moe 2 \1\5
6 5 Larry 3 \1\5\6
7 5 Curly 3 \1\5\7
8 5 Shemp 3 \1\5\8
9 8 Joe 4 \1\5\8\9
10 9 Curly Joe 5 \1\5\8\9\10



The "Hierarchical Path" contains the chain of command from the top level all the way down to the individual employee. If done in a Binary(4) format, you can easily use it to do some really remarkable things. It's kind of like the "new" HierarchyID datatype but better, IMHO.

For a much more detailed explanation of how that works, please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/72503/


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

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1427729
Posted Thursday, March 14, 2013 11:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
Looking, thank you!
Post #1431167
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse