November 19, 2020 at 11:00 pm
Hell community,
This is my first to try to use hierarchyID, but on my example above my column PATH return always NULL.
What i am doing wrong !??
CREATE TABLE #tbl(
ID int,
ParentIdINT,
NameVARCHAR(30),
[path] hierarchyid null
);
--DROP TABLE #tbl
INSERT INTO #tbl VALUES
(1,Null,'Corporate_HQ',null),
(2,1,'South_Region',null),
(3,1,'North_Region',null),
(4,1,'East_Region',null),
(5,1,'West_Region',null),
(6,3,'Chicago_District',null),
(7,3,'Milwaukee_District',null),
(8,3,'Minneapolis_District',null),
(9,6,'Gold_Coast_Dealer',null),
(10,6,'Blue_Island_Dealer',null);
with cte as (
select *,
--cast(concat('/', ID, '/') as varchar(max)) as [path]
CAST(concat(path.ToString() COLLATE DATABASE_DEFAULT, ID , '/') AS varchar(max)) AS [PATH]
from #tbl
where [ParentID] is null
union all
select child.*,
cast(concat(CONVERT(VARCHAR,parent.path ) COLLATE DATABASE_DEFAULT, child.ID , '/') as varchar(max)) as [path]
from #tbl as child
join cte as parent
on child.ParentID = parent.ID
)
update t
set path = c.path
from #tbl as t
join cte as c
on t.ID = c.ID;
SELECT * FROM #tbl t
November 20, 2020 at 1:05 pm
Well, I'm not really experienced with hierarchyid other than fiddling around with it. Most of what can be accomplished with hierarchyid can also be done in various other ways. In some projects we use nested JSON to store hierarchical data. The code you posted had a few issues. For clarity I created 2 tables. The first renames your 'tbl' table as 'adjacencies' and removes the NULL column for [path]. Then the 'hierarchies' table contains the calculated hierarchyid as [path].
drop table if exists #adjacencies;
go
create table #adjacencies(
id int,
parentid int,
area_name varchar(30));
go
INSERT INTO #adjacencies VALUES
(1,Null, 'Corporate_HQ'),
(2,1, 'South_Region'),
(3,1, 'North_Region'),
(4,1, 'East_Region'),
(5,1, 'West_Region'),
(6,3, 'Chicago_District'),
(7,3, 'Milwaukee_District'),
(8,3, 'Minneapolis_District'),
(9,6, 'Gold_Coast_Dealer'),
(10, 6, 'Blue_Island_Dealer');
drop table if exists #hierarchies;
go
create table #hierarchies(
id int,
parentid int,
area_name varchar(30),
[path] hierarchyid);
with recur_cte(ID, ParentId, area_name, [path]) as (
select ID, ParentId, area_name,
cast(concat('/', ID, '/') as hierarchyid)
from #adjacencies
where [ParentID] is null
union all
select child.ID, child.ParentId, child.area_name,
cast(concat(parent.[path].ToString(), child.ID, '/') as hierarchyid)
from #adjacencies as child
join recur_cte as parent on child.ParentID = parent.ID)
insert into #hierarchies
select *
from recur_cte;
select *, [path].ToString() as path_string,
[path].GetLevel() as path_level,
[path].GetAncestor(1).ToString() as path_ancestor_1,
[path].GetAncestor(2).ToString() as path_ancestor_2
from #hierarchies;
Output
idparentidarea_namepathpath_stringpath_levelpath_ancestor_1path_ancestor_2
1NULLCorporate_HQ0x58/1/1/NULL
21South_Region0x5B40/1/2/2/1//
31North_Region0x5BC0/1/3/2/1//
41East_Region0x5C20/1/4/2/1//
51West_Region0x5C60/1/5/2/1//
63Chicago_District0x5BE5/1/3/6/3/1/3//1/
73Milwaukee_District0x5BE7/1/3/7/3/1/3//1/
83Minneapolis_District0x5BE880/1/3/8/3/1/3//1/
96Gold_Coast_Dealer0x5BE5A6/1/3/6/9/4/1/3/6//1/3/
106Blue_Island_Dealer0x5BE5AA/1/3/6/10/4/1/3/6//1/3/
To get ancestors (grandchildren) for a particular area
declare @CurrentArea hierarchyid;
select @CurrentArea = [path]
from #hierarchies
where area_name = 'Corporate_HQ'
select *, [path].ToString() as path_string,
[path].GetLevel() as path_level
from #hierarchies
where [path].GetAncestor(2) = @CurrentArea;
Output
idparentidarea_namepathpath_stringpath_level
63Chicago_District0x5BE5/1/3/6/3
73Milwaukee_District0x5BE7/1/3/7/3
83Minneapolis_District0x5BE880/1/3/8/3
It's based on a Stack Overflow answer I bookmarked. It would be interesting to learn how you intend to use this.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 20, 2020 at 3:25 pm
Hello Steve,
Many thanks for your great explanation an examples that you send.
I plan to use hierarchies to classify my accounting chart of accounts. I am using Power bi to create financial analysis, namely the balance sheet and income statement. I know that in Power bi using DAX I can create hierarchies, however as all my clients' plans are different, having the query of SQL already prepared for this makes everything easier.
I happened to see another example that doesn't use HierachyID, on Stackoverflow :
https://stackoverflow.com/questions/36784211/select-all-hierarchy-level-and-below-sql-server
but my point is that I don't want to assign a value to a variable to start creating hierarchies for all accounts in my chart of accounts.
Any ideas for that?
Many thanks,
Luis
November 20, 2020 at 6:31 pm
but my point is that I don't want to assign a value to a variable to start creating hierarchies for all accounts in my chart of accounts. Any ideas for that?
Because many accounts would have the same hierarchyid? It depends on how the adjacencies are organized. Maybe the unique hierarchies could be calculated in a temp table and then joined back to the accounts table where the hierarchyid could be used for different aggregations.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 20, 2020 at 7:44 pm
Personally, I don't care for the use of HierarchyID nor it's particular form of positional notation. It's also a bugger to fix if something goes wrong with it.
Perhaps the following would be the better way to go because it provides the ease-of-maintenance of an Adjacency List, the strange and wonderful capabilities of a Hierarchical Path, and the nasty fast performance of Nested Sets. Part 2 actually creates a kind of hierarchical data warehouse. Both sets of code have been recently tested on modern hardware and both can create the results of a million node "proper" Adjacency List to all the other stuff in 19 seconds flat.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 23, 2020 at 11:04 am
Hello Steve,
Thanks for your reply.
Best regards,
Luis
November 23, 2020 at 11:08 am
Hello Jeff
Many thanks for all the documentation that you send me and your useful advise.
I will try the scripts to see which one fits best what I intend to do, and also understand better how to do it
Best regards,
Luis
November 23, 2020 at 4:37 pm
Every chart of accounts I have ever seen uses a hierarchical encoding scheme. I'm going to assume you've been to all library and seen the Dewey Decimal Classification codes for books. There is no need for using a rather ugly proprietary feature, like hierarchyID in SQL Server. Your company probably has such a scheme in place if they use any of the standard bookkeeping packages. If not, you can go on the Internet and find books that list such schemes..
Please post DDL and follow ANSI/ISO standards when asking for help.
November 23, 2020 at 4:49 pm
Joe's response brings up an excellent point... why not simply buy a proven product that does all of what you've asked plus so much more? For example (although I've not personally used it for years and they may have changed the nabe), "Quick Books" did all the classic accounting (such as "double entry", etc) and also has the ability to print checks, etc, etc. IIRC, it also is capable of printing quarterly tax documents and some decent payroll functionality, if needed.
And, if absolutely required, it'll do exports that can be imported into SQL Server to support Power BI, etc, although it also has some pretty good charting capabilities itself.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply