Encoding Tree Problem - 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

General SQL Server Forums

New to SQL Server Programming

Encoding Tree Problem

Author

Topic

udaaf
Starting Member

22 Posts

Posted - 2014-02-11 : 22:22:37

I get this original source code form @Swapeso and modify the code base on my requirement.
In this code I use ItemNumber for materialized path. If the depth of level just 3 (starting from 1) the result is correct. But if until > 3 the result is wrong. Could some explain me where's the problem and how to solve this problem.
Here's the code
Code with correct result :


--select *from tblBOMStructed 
DECLARE @BOMStructure TABLE 
(
PartNumber varchar(14)not null ,
Descript varchar(50)not null,
Qty integer not null default 0,
Price Decimal (10,2) default 0,
TotalPrice Decimal (10,2) default 0,
ItemNumber varchar(14) not null primary key 
)

INSERT @BOMStructure 
(PartNumber ,Descript ,Qty ,Price ,ItemNumber)
VALUES	('14300100001029','BASIC TANK',1,0,'1'),
		('11012142200503','SHELL',1,789.89,'1.1'),
		('12052140503','TOP CONE',1,226.75,'1.2'),
		('13052140503','BOTTOM CONE',1,226.75,'1.3'),
		('140104116508','PIPE LEG',3,39.75,'1.4'),
		('15004104','BALL FEET',3,0,'1.5'),
		('1510413504','SLEEVE',1,18.03,'1.5.1'),
		('1524809510','ADJUSTABLE BOLT',1,12.82,'1.5.2'),
		('1530411604','BASE',1,7.27,'1.5.3')
-- Mengupdate 
update @BOMStructure
set TotalPrice   = 0 
where PartNumber in 
(
select PartNumber  
from @BOMStructure   
);
		
-- Mengisi Table Total Price
update @BOMStructure 
set TotalPrice = Price * Qty;

-- Mengupdate Sub Assy Dan Main Assy di kalikan dengan qty
WITH cteParents(ItemNumber)
AS	(
	SELECT	ItemNumber
	FROM	@BOMStructure 
	WHERE	partnumber in	(
								select e1.PartNumber from @BOMStructure  e1,@BOMStructure  e2
								where	e2.ItemNumber > e1 .ItemNumber 
										and e2.ItemNumber < e1 .ItemNumber + 'Z'
										and e1 .ItemNumber not like '1'
										and e2  .ItemNumber Not like  '1'
								group by e1.PartNumber 
							)
	), cteSource(ItemNumber, TotalPrice)
AS (
	SELECT		p.ItemNumber,
			SUM(COALESCE(s.TotalPrice , 0)) AS TotalPrice
	FROM		cteParents AS p
	LEFT JOIN	(
				SELECT	ItemNumber AS Original,
					CASE
						WHEN ItemNumber LIKE '%.%' THEN ItemNumber
						ELSE '1.' + ItemNumber
					END AS ItemNumber,
					TotalPrice = Price * Qty  
				FROM	@BOMStructure  
			) AS s ON s.ItemNumber LIKE p.ItemNumber + '.%'
				OR p.ItemNumber = '1'--'0'
	WHERE		s.Original NOT IN (SELECT ItemNumber FROM cteParents)
	GROUP BY	p.ItemNumber
)
UPDATE		s
SET		s.TotalPrice  = q.TotalPrice * s.Qty  
FROM		@BOMStructure   AS s
INNER JOIN	cteSource AS q ON q.ItemNumber = s.ItemNumber;
 
--Mengupdate Harga Main Assy menggunakan function With 
with cteLevel(Lvl, PartNumber, TotalPrice)
AS
(
select LEN (ItemNumber)- LEN(REPLACE(ItemNumber, '.', ''))as Lvl, PartNumber,TotalPrice from @BOMStructure 
)
update s 
set s.TotalPrice = (select sum(TotalPrice )from cteLevel as PriceLvl1 where Lvl = 1)
from @BOMStructure as s
INNER JOIN	cteLevel  AS q ON q.PartNumber  = s.PartNumber 
where s.ItemNumber = '1'

select PartNumber, Descript , Qty , Price , TotalPrice , ItemNumber  
from @BOMStructure

code with wrong result


--select *from tblBOMStructed 
DECLARE @BOMStructure TABLE 
(
PartNumber varchar(14)not null ,
Descript varchar(50)not null,
Qty integer not null default 0,
Price Decimal (10,2) default 0,
TotalPrice Decimal (10,2) default 0,
ItemNumber varchar(14) not null primary key 
)

INSERT @BOMStructure 
(PartNumber ,Descript ,Qty ,Price ,ItemNumber)
VALUES	('14300100001029','ATMOSPHERIC TANK',1,0,'1'),
		('14300100001029','BASIC TANK',1,0,'1.1'),
		('11012142200503','SHELL',1,789.89,'1.1.1'),
		('12052140503','TOP CONE',1,226.75,'1.1.2'),
		('13052140503','BOTTOM CONE',1,226.75,'1.1.3'),
		('140104116508','PIPE LEG',3,39.75,'1.1.4'),
		('15004104','BALL FEET',3,0,'1.1.5'),
		('1510413504','SLEEVE',1,18.03,'1.1.5.1'),
		('1524809510','ADJUSTABLE BOLT',1,12.82,'1.1.5.2'),
		('1530411604','BASE',1,7.27,'1.1.5.3')
-- Mengupdate 
update @BOMStructure
set TotalPrice   = 0 
where PartNumber in 
(
select PartNumber  
from @BOMStructure   
);
		
-- Mengisi Table Total Price
update @BOMStructure 
set TotalPrice = Price * Qty;

-- Mengupdate Sub Assy Dan Main Assy di kalikan dengan qty
WITH cteParents(ItemNumber)
AS	(
	SELECT	ItemNumber
	FROM	@BOMStructure 
	WHERE	partnumber in	(
								select e1.PartNumber from @BOMStructure  e1,@BOMStructure  e2
								where	e2.ItemNumber > e1 .ItemNumber 
										and e2.ItemNumber < e1 .ItemNumber + 'Z'
										and e1 .ItemNumber not like '1'
										and e2  .ItemNumber Not like  '1'
								group by e1.PartNumber 
							)
	), cteSource(ItemNumber, TotalPrice)
AS (
	SELECT		p.ItemNumber,
			SUM(COALESCE(s.TotalPrice , 0)) AS TotalPrice
	FROM		cteParents AS p
	LEFT JOIN	(
				SELECT	ItemNumber AS Original,
					CASE
						WHEN ItemNumber LIKE '%.%' THEN ItemNumber
						ELSE '1.' + ItemNumber
					END AS ItemNumber,
					TotalPrice = Price * Qty  
				FROM	@BOMStructure  
			) AS s ON s.ItemNumber LIKE p.ItemNumber + '.%'
				OR p.ItemNumber = '1'--'0'
	WHERE		s.Original NOT IN (SELECT ItemNumber FROM cteParents)
	GROUP BY	p.ItemNumber
)
UPDATE		s
SET		s.TotalPrice  = q.TotalPrice * s.Qty  
FROM		@BOMStructure   AS s
INNER JOIN	cteSource AS q ON q.ItemNumber = s.ItemNumber;
 
--Mengupdate Harga Main Assy menggunakan function With 
with cteLevel(Lvl, PartNumber, TotalPrice)
AS
(
select LEN (ItemNumber)- LEN(REPLACE(ItemNumber, '.', ''))as Lvl, PartNumber,TotalPrice from @BOMStructure 
)
update s 
set s.TotalPrice = (select sum(TotalPrice )from cteLevel as PriceLvl1 where Lvl = 1)
from @BOMStructure as s
INNER JOIN	cteLevel  AS q ON q.PartNumber  = s.PartNumber 
where s.ItemNumber = '1'

select PartNumber, Descript , Qty , Price , TotalPrice , ItemNumber  
from @BOMStructure

Thanks Before,

UdaAf

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-02-12 : 04:21:07

[code]
)
UPDATE s
SET s.TotalPrice = q.TotalPrice * s.Qty
FROM @BOMStructure AS s
INNER JOIN cteSource AS q ON q.ItemNumber = s.ItemNumber;
[/code]

udaaf
Starting Member

22 Posts

Posted - 2014-02-12 : 20:31:36

quote:

Originally posted by waterduck


)
UPDATE		s
SET		s.TotalPrice  = q.TotalPrice * s.Qty  
FROM		@BOMStructure   AS s
INNER JOIN	cteSource AS q ON q.ItemNumber = s.ItemNumber;

Thanks for your reply. But the result still wrong.

Regards,

Afri

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-02-12 : 20:33:12

why wrong? using yours sample, it seem to be correct.

udaaf
Starting Member

22 Posts

Posted - 2014-02-12 : 20:35:20

udaaf
Starting Member

22 Posts

Posted - 2014-02-13 : 20:35:24

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources