Re: Hierarchical Top Level BOM Query

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Itzik Ben-Gan (itzik_at_REMOVETHIS.SolidQualityLearning.com)
Date: 02/27/05 

Date: Sun, 27 Feb 2005 12:34:07 +0100

Check out solutions to BOM in the following whitepaper:

(URL may wrap)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_05TSQLEnhance.asp

Even though the solutions in the paper use Recursive CTEs in SQL Server
2005, they can all be easily converted to UDFs in SQL Server 2000 applying
the same algorithms. If you have any trouble converting to UDFs, let me
know, and I can give you examples.

Cheers, 

-- 
BG, SQL Server MVP
www.SolidQualityLearning.com
"CapeCAD" <CapeCAD@discussions.microsoft.com> wrote in message 
news:0C850AAC-50A8-4BBF-A60A-366D510B7FA9@microsoft.com...
> The following query gives me some top level and some subassembly levels
> returned because they are at the same level in the hierarchy. How can I 
> get
> it to keep selecting the subassembly levels until all returns are top 
> level?
>
> SELECT DISTINCT TOP 100 PERCENT Parent_Pfl.PflNumber AS ParentPfl,
> Child_Pfl.PflNumber AS ChildProfile
> FROM   (SELECT     ParentPflHierarchy.ParentPflID,
> ChildPflHierarchy.ChildPflID
>             FROM          dbo.PflHierarchy ChildPflHierarchy INNER JOIN
>                               dbo.PflHierarchy ParentPflHierarchy ON
> ChildPflHierarchy.ParentPflID = ParentPflHierarchy.ChildPflID) DERIVEDTBL
> INNER JOIN
>                      dbo.Pfl Parent_Pfl ON DERIVEDTBL.ParentPflID =
> Parent_Pfl.PflID INNER JOIN
>                      dbo.Pfl Child_Pfl ON DERIVEDTBL.ChildPflID =
> Child_Pfl.PflID
> WHERE     (Child_Pfl.PflNumber = N'28699')
> ORDER BY Parent_Pfl.PflNumber
>
> PflHierarchy Table
> PflHierarchyID ParentPflID    ChildPflID
> 1                    1       2
> 2                    1       3
> 3                    1       4
> 4                    1       5
> 5                    2       6
> 6                    2       7
> 7                    6       8
> 8                    6       9
> 9                    6       10
>
> Pfl Table
> PflID Description
> 1 This is a top level assy
> 2 This is a Sub assy
> 3 This is a child level for a part
> 4 This is a child level for a part
> 5 This is a child level for a part
> 6 This is a Sub assy
> 7 This is a child level for a part
> 8 This is a child level for a part
> 9 This is a child level for a part
> 10 This is a child level for a part
>