Hierarchies - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Hierarchies

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2004-01-15 : 01:43:30

Let me say first that I have read and re-read every article, post or link I have found on your website concerning tables that have heiarchial data. While I have learned a great deal, I still do not know how to solve my problem, so I am hoping that you experts can tell me what I am missing in all of this.

I have about four different tables that have hiearchy data. I am going to use one, the most complex in my example, and hope I can learn from it to apply to the other. We have used this table structure for quite some time now, but as we reach the end of our production cycle, the reports team is now beginning to try to report data out of our system. While the table structure works great for us, the guys writing reports are having a terrible time.

We have a table that would have three levels (Dept, Subdept and RevCode). The Parent field would hold the Identity value of it's parent. We have not implemented the adjacency model that is listed in all the articles because this has never been an issue before this reporting came up.

I thought I had this resolved last night with the query below, until I was going to post a question to you guys about sorting within a node (which is another headache). However when I created the sample data below, it no longer was in "tree order". The data on our actual table must be keyed in "tree order" for it to come out that way. Which leads me to today and the second full day of surfing the web trying to find an answer. I am at a loss.

What I need is a result set that is in "tree" order. The Depts, then all of its SubDepts, then all of the SubDept's revCode, etc.
If anyone has any comments, suggestions, or additional links (althought I am not sure there are any left :)), I would greatly appreciate them.

Thanks so much,
JAdauto

SELECT RevCode.*
FROM RevenueCode AS RevCode
LEFT JOIN RevenueCode AS SubDept
ON RevCode.Parent = SubDept.ID
LEFT JOIN Revenuecode as Dept
ON Subdept.Parent = Dept.ID

---create sample table----
CREATE TABLE [dbo].[RevenueCode] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[RevenueCodeTypeENUM] [smallint] NOT NULL ,
[Parent] [int] NOT NULL ,
[Code] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

---insert sample data---
INSERT INTO RevenueCode (RevenueCodeTypeENUM, Parent, Code, Description) VALUES (1, 0, 'M1', 'MainDept1')
INSERT INTO RevenueCode (RevenueCodeTypeENUM, Parent, Code, Description) VALUES (1, 0, 'M2', 'MainDept2')
INSERT INTO RevenueCode (RevenuecodeTypeENUM, Parent, Code, Description) VALUES (2, 1, 'SA', 'SubDeptA')
INSERT INTO RevenueCode (RevenuecodeTypeENUM, Parent, Code, Description) VALUES (2, 2, 'SB', 'SubDeptB')
INSERT INTO RevenueCode (RevenuecodeTypeENUM, Parent, Code, Description) VALUES (3, 3, '100', 'AFood')
INSERT INTO RevenueCode (RevenuecodeTypeENUM, Parent, Code, Description) VALUES (3, 3,' 400', 'ALiquor')
INSERT INTO RevenueCode (RevenuecodeTypeENUM, Parent, Code, Description) VALUES (3, 3, '200', 'ABeer')
INSERT INTO RevenueCode (RevenuecodeTypeENUM, Parent, Code, Description) VALUES (3, 3, '300', 'AWine')
INSERT INTO RevenueCode (RevenuecodeTypeENUM, Parent, Code, Description) VALUES (3, 4, '100', 'BFood')
INSERT INTO RevenueCode (RevenuecodeTypeENUM, Parent, Code, Description) VALUES (3, 4, '400', 'BLiquor')
INSERT INTO RevenueCode (RevenuecodeTypeENUM, Parent, Code, Description) VALUES (3, 4, '200', 'BBeer')
INSERT INTO RevenueCode (RevenuecodeTypeENUM, Parent, Code, Description) VALUES (3, 4, '300', 'BWine')

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2004-01-15 : 02:50:14

My guess is that you have a typical treview-node problem what bothers me though, from looking at the the DDL you sent, is that the data somehow doesn't fit.
My humble oppinion is that the child nodes can't have the same values that appear in parent nodes and vice versa (3,3 or 2,2...), or do you have a explanation for this?

There is a article on hierarchy http://www.sqlteam.com/item.asp?ItemID=8866, read this first.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-01-15 : 04:10:39

How about this?


SELECT 
  RevCode.ID,
  RevCode.RevenueCodeTypeENUM as Part1,
  RevCode.Parent,
  RevCode.Code,
  RevCode.Description,
  (
  right('0000000000' + 
  convert(varchar(10),
    CASE
      WHEN RevCode.Parent = 0 
      THEN RevCode.ID 
      ELSE 
        CASE 
          WHEN (SELECT Parent FROM RevenueCode C WHERE RevCode.Parent = C.ID) = 0
            THEN (SELECT ID FROM RevenueCode B WHERE RevCode.Parent = B.ID)
          ELSE
            (SELECT Parent FROM RevenueCode C WHERE RevCode.Parent = C.ID)
        END
    END
    ), 10)
    +
  convert(varchar(10), 
    CASE
      WHEN RevCode.Parent = 0 
        THEN 0
      WHEN (SELECT Parent FROM RevenueCode C WHERE RevCode.Parent = C.ID) = 0
        THEN 8
        ELSE 9
    END
    )
  ) as SortOrder

FROM RevenueCode AS RevCode
  LEFT JOIN RevenueCode AS SubDept ON RevCode.Parent = SubDept.ID
  LEFT JOIN Revenuecode as Dept ON Subdept.Parent = Dept.ID

ORDER BY
  6, 1

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

JAdauto
Posting Yak Master

160 Posts

Posted - 2004-01-15 : 11:34:27

quote:
Originally posted by rihardh

My guess is that you have a typical treview-node problem what bothers me though, from looking at the the DDL you sent, is that the data somehow doesn't fit.
My humble oppinion is that the child nodes can't have the same values that appear in parent nodes and vice versa (3,3 or 2,2...), or do you have a explanation for this?

I dont think I understand the question. On my sample data, MainDept1 is a parent node. SubDeptA is a parent node but also a child node of MainDept1. 100 A Food is a child node of SubDeptA along with several other children records (200,300,400). The same scenerio with MainDept2, SubDeptB, and the four revenue codes that begin with a B. I am not sure what you mean by "the data does not fit".

quote:
Originally posted by rihardh
There is a article on hierarchy http://www.sqlteam.com/item.asp?ItemID=8866, read this first.

This link did not seem to work.

JAdauto
Posting Yak Master

160 Posts

Posted - 2004-01-15 : 11:42:45

AjarnMark - this certainly worked! It returned the result set in the order that I needed and I am indebted. I have not the foggiest idea how you did it though. I am trying to break this apart, but I think it is above my level of expertise. :) As usual, and I grateful to you and the SqlTeam experts.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-01-15 : 13:50:03

JAdauto,

As for rihardh's comments and link, his link had an extra comma at the end. Without the comma, it looks like this: http://www.sqlteam.com/item.asp?ItemID=8866

And when I first looked at your data, I was thrown off by the repeating ENUM values, and then realized that it was the code that really separated things. (BTW, you don't really have Description in your table do you? I assume that was just for illustrative purposes and that it is really in a child table keyed off of Code).

The approach I took was to build a Sort_Order column that. In order for it to work, you have to retrieve the ID of the top-level parent record as the first part of the sort-sequence. So the first part basically says "If I'm the top-level (a.k.a. my parent = 0) then use my ID. Otherwise if my parent is the top-level, use his ID, otherwise get my grand-parent's ID. Note that since you specified there will only be a maximum of 3 levels, this can all be accomplished within the CASE statement. If you had more, this would get way too unwieldy to use.

Now, once we have the beginning of the sort sequence, we go into second-level and at this point I got tired and hard-coded values to indicate whether the row was a parent(0), child(8) or grand-child(9). Ideally I would have put in the appropriate next level, but since it was 1:30 in the morning...

And finally, the right('0000000000' + ...,10) syntax is because I don't know how large your top-level ID's will become, so I wanted to provide leading zeroes on order to make top-level ID 01 and 02 come before 10 and 11. I just took a stab and said to myself 10 digits is enough for the example. Then, sort it by the new column (position 6), but that is not unique the way I built it so do a secondary sort by the record's ID.

Hope that helps you understand my approach.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources