none
Query Tree dengan Hierarchyid RRS feed

  • Pertanyaan

  • Salam kenal semuanya. Saya ada kendala untuk query tree. Dah 2 metoda di coba baik menggunakan CTE ataupun hierarchyid. Dimana jikalau qty > 1 hasil yang di parent dan ujung2nya di root pasti salah. Ini code nya.
    Mohon bantuannya ya.

    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;
    

    [IMG]http://i1214.photobucket.com/albums/cc482/udaaf/HierarchyId_zps13b787ba.jpg[/IMG]
    Sabtu, 22 Februari 2014 10.15

Jawaban