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 2008 Forums
 Transact-SQL (2008)
 Tree Query with Hierarchyid

Author  Topic 

udaaf
Starting Member

22 Posts

Posted - 2014-02-22 : 05:29:29
I have problem with tree query where if qty > 1 the result is fail.
Could someone explain me how to solve this problem.
Here's the code :


declare @BOMStructure as table
(
PartNumber varchar(14)not null ,
Descript varchar(50)not null,
Qty integer not null default 0,
Price Decimal (10,2) default 0,
ItemNumber hierarchyid not null primary key
)

INSERT @BOMStructure
(PartNumber ,Descript ,Qty ,Price ,ItemNumber)
VALUES ('00150060060005','BASIC TANK',1,0,'/'),
('11012142200503','SHELL',3,100,'/1/'),
('12052140503','TOP CONE',1,0,'/2/'),
('13052140503','BOTTOM CONE',2,100,'/2/1/'),
('140104116508','PIPE LEG',3,50,'/2/2/'),
('1510413504','SLEEVE',2,0,'/3/'),
('1524809510','ADJUSTABLE BOLT',1,100,'/3/1/'),
('1530411604','BASE',1,100,'/3/2/')


-- GetAncestor
-- Mengupdate
select PartNumber, Descript,Qty,Price,
(select sum (Price * qty)
from @BOMStructure
where ItemNumber .IsDescendantOf (p.ItemNumber ) = 1
) as [TotalPrice],
ItemNumber .ToString() as [Hierarcy], ItemNumber .GetLevel() as [Level]
from @BOMStructure as P;


nagino
Yak Posting Veteran

75 Posts

Posted - 2014-02-23 : 20:53:47
What is your expected result?
At Intermediate node, multiply Qty together sum of child node's TotalPrice, like following?


WITH Work (PartNumber, Descript, Qty, Price, SubTotalPrice, ItemNumber) AS (
SELECT PartNumber, Descript, Qty, Price, Price * Qty, ItemNumber
FROM @BOMStructure Base
WHERE NOT EXISTS (
SELECT * FROM @BOMStructure Filter
WHERE Filter.ItemNumber.GetAncestor(1) = Base.ItemNumber)
UNION ALL
SELECT Base.PartNumber, Base.Descript, Base.Qty, Base.Price, Convert(Decimal(10, 2), Work.SubTotalPrice) * Base.Qty, Base.ItemNumber
FROM @BOMStructure Base
INNER JOIN Work
ON Work.ItemNumber.GetAncestor(1) = Base.ItemNumber
)
SELECT PartNumber, Descript, Qty, Price, SUM(SubTotalPrice) TotalPrice, ItemNumber.ToString() [Hierarcy], ItemNumber.GetLevel() [Level] FROM Work
GROUP BY PartNumber, Descript, Qty, Price, ItemNumber
ORDER BY ItemNumber


-------------------------------------
From Japan
Sorry, my English ability is limited.
Go to Top of Page

udaaf
Starting Member

22 Posts

Posted - 2014-02-24 : 23:19:10
@Nagino,

Thanks for your time and script.
Case closed :)
Go to Top of Page
   

- Advertisement -