Tree Query Problem

Hello Afri,

Can you test this script, I used cursor finally. I hope that's okay as none other solutions seems to be working for you.

IF object_ID('tempdb..#cteParents') IS NOT NULL
       drop table #cteParents
--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'),
                ('00150060060005','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;
                
-- Mengisi Table Total Price
update @BOMStructure 
set TotalPrice = Price * Qty;

-- Mengupdate Sub Assy Dan Main Assy di kalikan dengan qty
WITH cteParents(ItemNumber,LVL)
AS      (
        SELECT  ItemNumber,cast(LEN(ItemNumber)-len(replace(ItemNumber,'.','')) as int) LVL
        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 
                ))
        SELECT * INTO #cteParents from cteParents
        --SELECT * from #cteParents 

        DECLARE cteParents CURSOR  FOR
        SELECT * FROM #cteParents ORDER BY LVL DESC

        declare @itemnumber varchar(100),@lvl int,@sum decimal(10,2);
        OPEN cteParents
        FETCH NEXT FROM cteParents INTO @itemnumber,@lvl

        WHILE @@FETCH_STATUS = 0   
        BEGIN           

                select @sum=sum(b.Price*b.Qty) FROM             @BOMStructure   AS b
                WHERE b.ItemNumber like @itemnumber+'.%' and cast(LEN(b.ItemNumber)-len(replace(b.ItemNumber,'.','')) as int)=@lvl+1;

                update @BOMStructure 
                set TotalPrice=@sum*Qty,Price=@sum
                where ItemNumber=@itemnumber

                --select * from @BOMStructure
  FETCH NEXT FROM cteParents INTO @itemnumber,@lvl
        END   

CLOSE cteParents;
DEALLOCATE cteParents;
 
--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'

update @BOMStructure
set Price = TotalPrice / Qty 
--Kondisi Part Number yang merupakan Sub Assembly
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 )
update @BOMStructure
set Price = TotalPrice / Qty 
where ItemNumber = '1'
select PartNumber, Descript , Qty , Price , TotalPrice , ItemNumber  
from @BOMStructure  

---

Satheesh

My Blog | How to ask questions in technical forum