Hierarchical Top Level BOM Query

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 partGet a copy of TREES & HIERARCHIES IN SQL. I worked out a BOM using the
nested sets model. It will run at least an order of magnitude faster
and let you do hierarchical summaries in a single query.|||I ordered TREES & HIERARCHIES IN SQL, along with Joe Celko's SQL for
Smarties: Advanced SQL Programming, but they won't be here until Tuesday fro
m
Amazon.com. There's none available in any local stores.
One problem is I'm working with data imported from an SQL Anywhere database,
and I can't make any chages to the tables because they will be running both
databases concurrently.
I'm an application programmer, but I've had to deal with SQL due to lack of
support from our DB administrator.
I'll hopefully have this worked out by then, but I'll let you know if I get
any further information from it.
Thank you
"--CELKO--" wrote:

> Get a copy of TREES & HIERARCHIES IN SQL. I worked out a BOM using the
> nested sets model. It will run at least an order of magnitude faster
> and let you do hierarchical summaries in a single query.
>|||I ordered TREES & HIERARCHIES IN SQL along with SQL FOR SMARTIES from
Amazon.com and they'll be here Tuesday, there are none available from any
local bookstores.
One problem I'm dealing with is the database is imported from SQL Anywhere
and no changes can be made to the structure so they can be moved back and
forth.
I'm primarily an application programmer, but I've had to deal with SQL due
to lack of support from our DB Administrator.
I'll hopefully have this worked out before the books come Tuesday, but I'll
let you know if I get any usefull information from them.
Thanks
"--CELKO--" wrote:

> Get a copy of TREES & HIERARCHIES IN SQL. I worked out a BOM using the
> nested sets model. It will run at least an order of magnitude faster
> and let you do hierarchical summaries in a single query.
>|||Check out solutions to BOM in the following whitepaper:
(URL may wrap)
http://msdn.microsoft.com/library/d...TSQLEnhance.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
>|||I would appreciate seeing some examples, I think that may help.
I've taken 15 different approaches now and the results are all the same. If
an item is 3 levels deep in one BOM and 7 levels deep in another: I get the
top level from the 3 level deep, and the 4th level from the 7 level deep.
Thank you
"Itzik Ben-Gan" wrote:

> Check out solutions to BOM in the following whitepaper:
> (URL may wrap)
> http://msdn.microsoft.com/library/d...TSQLEnhance.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...
>
>|||Sure,
Here are solutions to "Assemblies containing a certain sub-item" using both
Recursive CTEs in SQL Server 2005 and using UDFs in SQL Server 2000:
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('BOM') IS NOT NULL
DROP TABLE BOM;
GO
IF OBJECT_ID('Items') IS NOT NULL
DROP TABLE Items;
GO
CREATE TABLE Items
(
itemid VARCHAR(5) NOT NULL PRIMARY KEY,
itemname VARCHAR(25) NOT NULL,
/* other columns, e.g., unit_price, measurement_unit */
);
CREATE TABLE BOM
(
itemid VARCHAR(5) NOT NULL REFERENCES Items,
assemblyid VARCHAR(5) NULL REFERENCES Items,
qty INT NOT NULL
/* other columns, e.g., quantity */
UNIQUE CLUSTERED(itemid, assemblyid),
CHECK (itemid <> assemblyid)
);
INSERT INTO Items(itemid, itemname) VALUES('A', 'Item A');
INSERT INTO Items(itemid, itemname) VALUES('B', 'Item B');
INSERT INTO Items(itemid, itemname) VALUES('C', 'Item C');
INSERT INTO Items(itemid, itemname) VALUES('D', 'Item D');
INSERT INTO Items(itemid, itemname) VALUES('E', 'Item E');
INSERT INTO Items(itemid, itemname) VALUES('F', 'Item F');
INSERT INTO Items(itemid, itemname) VALUES('G', 'Item G');
INSERT INTO Items(itemid, itemname) VALUES('H', 'Item H');
INSERT INTO Items(itemid, itemname) VALUES('I', 'Item I');
INSERT INTO Items(itemid, itemname) VALUES('J', 'Item J');
INSERT INTO Items(itemid, itemname) VALUES('K', 'Item K');
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'A', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'B', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'C', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'D', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'E', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'F', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'G', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'H', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'I', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'J', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'K', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('E', 'J', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('C', 'E', 3);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'C', 2);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('H', 'C', 4);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('C', 'B', 2);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('B', 'F', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('B', 'G', 3);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'B', 2);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'D', 2);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('H', 'I', 1);
GO
-- SQL Server 2005 Solution
DECLARE @.itemid AS VARCHAR(5);
SET @.itemid = 'F';
WITH BOMCTE
AS
(
SELECT itemid, assemblyid, qty,
0 AS lvl, CAST('.' + itemid + '.' AS VARCHAR(900)) AS path
FROM BOM
WHERE itemid = @.itemid
UNION ALL
SELECT BOM.itemid, BOM.assemblyid, BOMCTE.qty * BOM.qty,
BOMCTE.lvl + 1, CAST('.' + BOM.itemid + BOMCTE.path AS VARCHAR(900))
FROM BOMCTE
JOIN BOM
ON BOM.itemid = BOMCTE.assemblyid
)
SELECT itemid, qty, lvl, path
FROM BOMCTE
WHERE assemblyid IS NULL
AND lvl > 0;
Output:
itemid qty lvl path
-- -- -- --
B 1 1 .B.F.
C 2 2 .C.B.F.
H 8 3 .H.C.B.F.
A 4 3 .A.C.B.F.
A 2 2 .A.B.F.
-- SQL Server 2000 Solution
IF OBJECT_ID('fn_AssembliesContainingItem')
IS NOT NULL
DROP FUNCTION fn_AssembliesContainingItem;
GO
CREATE FUNCTION fn_AssembliesContainingItem(@.itemid AS VARCHAR(5))
RETURNS @.T TABLE
(
itemid VARCHAR(5) NOT NULL,
assemblyid VARCHAR(5) NULL,
qty INT NOT NULL,
lvl INT NOT NULL,
path VARCHAR(900) NOT NULL,
UNIQUE CLUSTERED(lvl, assemblyid, itemid)
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0;
INSERT INTO @.T
SELECT itemid, assemblyid, qty, @.lvl, '.' + itemid + '.'
FROM BOM
WHERE itemid = @.itemid;
WHILE @.@.rowcount > 0
BEGIN
SET @.lvl = @.lvl + 1;
INSERT INTO @.T
SELECT BOM.itemid, BOM.assemblyid, T.qty * BOM.qty,
@.lvl, '.' + BOM.itemid + T.path
FROM @.T AS T JOIN BOM
ON T.lvl = @.lvl - 1
AND BOM.itemid = T.assemblyid;
END
DELETE FROM @.T WHERE lvl = 0 OR assemblyid IS NOT NULL;
RETURN
END
GO
SELECT itemid, qty, lvl, path FROM fn_AssembliesContainingItem('F');
Output:
itemid qty lvl path
-- -- -- --
B 1 1 .B.F.
A 2 2 .A.B.F.
C 2 2 .C.B.F.
A 4 3 .A.C.B.F.
H 8 3 .H.C.B.F.
BG, SQL Server MVP
www.SolidQualityLearning.com
"CapeCAD" <CapeCAD@.discussions.microsoft.com> wrote in message
news:A9C665B8-2CE1-4A88-8008-F29AB2D0B7B0@.microsoft.com...
>I would appreciate seeing some examples, I think that may help.
> I've taken 15 different approaches now and the results are all the same.
> If
> an item is 3 levels deep in one BOM and 7 levels deep in another: I get
> the
> top level from the 3 level deep, and the 4th level from the 7 level deep.
> Thank you
> "Itzik Ben-Gan" wrote:
>|||I'm not sure yet what I might have transposed wrong, but when I tried the
following:
CREATE FUNCTION [dbo].[fn_AssembliesContainingItem]
(@.itemid AS VARCHAR(14))
RETURNS @.T TABLE
(
ChildPflID VARCHAR(14) NOT NULL,
ParentPflID VARCHAR(14) NULL,
lvl INT NOT NULL,
UNIQUE CLUSTERED(lvl, ParentPflID, ChildPflID)
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0;
INSERT INTO @.T
SELECT PflHierarchy.ChildPflID, PflHierarchy.ParentPflID, @.lvl
FROM PflHierarchy
WHERE PflHierarchy.ChildPflID = @.itemid;
WHILE @.@.rowcount > 0
BEGIN
SET @.lvl = @.lvl + 1;
INSERT INTO @.T
SELECT PflHierarchy.ChildPflID, PflHierarchy.ParentPflID, @.lvl
FROM @.T AS T JOIN PflHierarchy
ON T.lvl = @.lvl - 1
AND PflHierarchy.ChildPflID = T.ParentPflID;
END
DELETE FROM @.T WHERE lvl = 0 OR ParentPflID IS NOT NULL;
RETURN
END
I get the following error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY
constraint 'UQ__@.T__20A07422'. Cannot insert duplicate key in object
'#1FAC4FE9'.
My books by Joe Celko just came in, so I'll be reading them for more ideas
to try.
"Itzik Ben-Gan" wrote:

> Sure,
> Here are solutions to "Assemblies containing a certain sub-item" using bot
h
> Recursive CTEs in SQL Server 2005 and using UDFs in SQL Server 2000:
> SET NOCOUNT ON;
> USE tempdb;
> GO
> IF OBJECT_ID('BOM') IS NOT NULL
> DROP TABLE BOM;
> GO
> IF OBJECT_ID('Items') IS NOT NULL
> DROP TABLE Items;
> GO
> CREATE TABLE Items
> (
> itemid VARCHAR(5) NOT NULL PRIMARY KEY,
> itemname VARCHAR(25) NOT NULL,
> /* other columns, e.g., unit_price, measurement_unit */
> );
> CREATE TABLE BOM
> (
> itemid VARCHAR(5) NOT NULL REFERENCES Items,
> assemblyid VARCHAR(5) NULL REFERENCES Items,
> qty INT NOT NULL
> /* other columns, e.g., quantity */
> UNIQUE CLUSTERED(itemid, assemblyid),
> CHECK (itemid <> assemblyid)
> );
> INSERT INTO Items(itemid, itemname) VALUES('A', 'Item A');
> INSERT INTO Items(itemid, itemname) VALUES('B', 'Item B');
> INSERT INTO Items(itemid, itemname) VALUES('C', 'Item C');
> INSERT INTO Items(itemid, itemname) VALUES('D', 'Item D');
> INSERT INTO Items(itemid, itemname) VALUES('E', 'Item E');
> INSERT INTO Items(itemid, itemname) VALUES('F', 'Item F');
> INSERT INTO Items(itemid, itemname) VALUES('G', 'Item G');
> INSERT INTO Items(itemid, itemname) VALUES('H', 'Item H');
> INSERT INTO Items(itemid, itemname) VALUES('I', 'Item I');
> INSERT INTO Items(itemid, itemname) VALUES('J', 'Item J');
> INSERT INTO Items(itemid, itemname) VALUES('K', 'Item K');
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'A', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'B', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'C', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'D', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'E', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'F', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'G', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'H', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'I', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'J', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'K', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('E', 'J', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('C', 'E', 3);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'C', 2);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('H', 'C', 4);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('C', 'B', 2);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('B', 'F', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('B', 'G', 3);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'B', 2);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'D', 2);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('H', 'I', 1);
> GO
> -- SQL Server 2005 Solution
> DECLARE @.itemid AS VARCHAR(5);
> SET @.itemid = 'F';
> WITH BOMCTE
> AS
> (
> SELECT itemid, assemblyid, qty,
> 0 AS lvl, CAST('.' + itemid + '.' AS VARCHAR(900)) AS path
> FROM BOM
> WHERE itemid = @.itemid
> UNION ALL
> SELECT BOM.itemid, BOM.assemblyid, BOMCTE.qty * BOM.qty,
> BOMCTE.lvl + 1, CAST('.' + BOM.itemid + BOMCTE.path AS VARCHAR(900))
> FROM BOMCTE
> JOIN BOM
> ON BOM.itemid = BOMCTE.assemblyid
> )
> SELECT itemid, qty, lvl, path
> FROM BOMCTE
> WHERE assemblyid IS NULL
> AND lvl > 0;
> Output:
> itemid qty lvl path
> -- -- -- --
> B 1 1 .B.F.
> C 2 2 .C.B.F.
> H 8 3 .H.C.B.F.
> A 4 3 .A.C.B.F.
> A 2 2 .A.B.F.
> -- SQL Server 2000 Solution
> IF OBJECT_ID('fn_AssembliesContainingItem')
IS NOT NULL
> DROP FUNCTION fn_AssembliesContainingItem;
> GO
> CREATE FUNCTION fn_AssembliesContainingItem(@.itemid AS VARCHAR(5))
> RETURNS @.T TABLE
> (
> itemid VARCHAR(5) NOT NULL,
> assemblyid VARCHAR(5) NULL,
> qty INT NOT NULL,
> lvl INT NOT NULL,
> path VARCHAR(900) NOT NULL,
> UNIQUE CLUSTERED(lvl, assemblyid, itemid)
> )
> AS
> BEGIN
> DECLARE @.lvl AS INT;
> SET @.lvl = 0;
> INSERT INTO @.T
> SELECT itemid, assemblyid, qty, @.lvl, '.' + itemid + '.'
> FROM BOM
> WHERE itemid = @.itemid;
> WHILE @.@.rowcount > 0
> BEGIN
> SET @.lvl = @.lvl + 1;
> INSERT INTO @.T
> SELECT BOM.itemid, BOM.assemblyid, T.qty * BOM.qty,
> @.lvl, '.' + BOM.itemid + T.path
> FROM @.T AS T JOIN BOM
> ON T.lvl = @.lvl - 1
> AND BOM.itemid = T.assemblyid;
> END
> DELETE FROM @.T WHERE lvl = 0 OR assemblyid IS NOT NULL;
> RETURN
> END
> GO
> SELECT itemid, qty, lvl, path FROM fn_AssembliesContainingItem('F');
> Output:
> itemid qty lvl path
> -- -- -- --
> B 1 1 .B.F.
> A 2 2 .A.B.F.
> C 2 2 .C.B.F.
> A 4 3 .A.C.B.F.
> H 8 3 .H.C.B.F.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "CapeCAD" <CapeCAD@.discussions.microsoft.com> wrote in message
> news:A9C665B8-2CE1-4A88-8008-F29AB2D0B7B0@.microsoft.com...
>
>|||Joe,
I've read your books, and perhaps I was not clear on what my initial problem
is:
1. I am working with existing tables and existing data
2. I am working with multiple parent Bills Of Materials
3. I am searching for All top level items when given a part item
The following demonstrates the problem:
CREATE TABLE [Hierarchy] (
[PflHierarchyID] [int] NOT NULL ,
[ParentPflID] [int] NOT NULL ,
[ChildPflID] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Part] (
[PflID] [int] NOT NULL ,
[PflNumber] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
INSERT INTO [Hierarchy] VALUES(1,1,2)
INSERT INTO [Hierarchy] VALUES(2,1,3)
INSERT INTO [Hierarchy] VALUES(3,3,2)
INSERT INTO [Hierarchy] VALUES(4,3,4)
INSERT INTO [Hierarchy] VALUES(5,5,2)
INSERT INTO [Hierarchy] VALUES(6,5,4)
INSERT INTO [Part] VALUES(1,'1234','PIII Motherboard')
INSERT INTO [Part] VALUES(2,'341','Res 1K 0805')
INSERT INTO [Part] VALUES(3,'2345','Video Card')
INSERT INTO [Part] VALUES(4,'421','Cap 1 pF 0805')
INSERT INTO [Part] VALUES(5,'12345','P4 Motherboard')
This view should return '1234','2345', and '12345': Returns '1234'
SELECT DISTINCT
TOP 100 PERCENT Parent_Pfl.PflNumber AS ParentPfl,
Parent_Pfl.Description AS ParentDescription, Child_Pfl.PflNumber AS
ChildProfile,
Child_Pfl.Description AS ChildDescription
FROM (SELECT ParentPflHierarchy.ParentPflID,
ChildPflHierarchy.ChildPflID
FROM Hierarchy ChildPflHierarchy INNER JOIN
Hierarchy ParentPflHierarchy
ON ParentPflHierarchy.ChildPflID = ChildPflHierarchy.ParentPflID)
HierarchyTBL INNER JOIN
dbo.Part Child_Pfl ON HierarchyTBL.ChildPflID =
Child_Pfl.PflID INNER JOIN
dbo.Part Parent_Pfl ON HierarchyTBL.ParentPflID =
Parent_Pfl.PflID
WHERE (Child_Pfl.PflNumber = N'421')
Any help would be appreciated
"--CELKO--" wrote:

> Get a copy of TREES & HIERARCHIES IN SQL. I worked out a BOM using the
> nested sets model. It will run at least an order of magnitude faster
> and let you do hierarchical summaries in a single query.
>|||I created a UNIQUE constraint to allow filtering using an index, but
multiple paths leading to the same node might end up with dups (which are
valid).
So either remove the constraint, or add an identity column at the end if you
want to allow using an index to filter the previous level:
CREATE FUNCTION fn_AssembliesContainingItem(@.itemid AS VARCHAR(5))
RETURNS @.T TABLE
(
itemid VARCHAR(5) NOT NULL,
assemblyid VARCHAR(5) NULL,
qty INT NOT NULL,
lvl INT NOT NULL,
path VARCHAR(900) NOT NULL,
surkey INT NOT NULL IDENTITY,
UNIQUE CLUSTERED(lvl, assemblyid, itemid, surkey)
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0;
INSERT INTO @.T
SELECT itemid, assemblyid, qty, @.lvl, '.' + itemid + '.'
FROM BOM
WHERE itemid = @.itemid;
WHILE @.@.rowcount > 0
BEGIN
SET @.lvl = @.lvl + 1;
INSERT INTO @.T
SELECT BOM.itemid, BOM.assemblyid, T.qty * BOM.qty,
@.lvl, '.' + BOM.itemid + T.path
FROM @.T AS T JOIN BOM
ON T.lvl = @.lvl - 1
AND BOM.itemid = T.assemblyid;
END
DELETE FROM @.T WHERE lvl = 0 OR assemblyid IS NOT NULL;
RETURN
END
GO
BG, SQL Server MVP
www.SolidQualityLearning.com
"CapeCAD" <CapeCAD@.discussions.microsoft.com> wrote in message
news:8E1A0DAE-D71A-4849-8FA3-75D3043AD644@.microsoft.com...
> I'm not sure yet what I might have transposed wrong, but when I tried the
> following:
> CREATE FUNCTION [dbo].[fn_AssembliesContainingItem]
> (@.itemid AS VARCHAR(14))
> RETURNS @.T TABLE
> (
> ChildPflID VARCHAR(14) NOT NULL,
> ParentPflID VARCHAR(14) NULL,
> lvl INT NOT NULL,
> UNIQUE CLUSTERED(lvl, ParentPflID, ChildPflID)
> )
> AS
> BEGIN
> DECLARE @.lvl AS INT;
> SET @.lvl = 0;
> INSERT INTO @.T
> SELECT PflHierarchy.ChildPflID, PflHierarchy.ParentPflID, @.lvl
> FROM PflHierarchy
> WHERE PflHierarchy.ChildPflID = @.itemid;
> WHILE @.@.rowcount > 0
> BEGIN
> SET @.lvl = @.lvl + 1;
> INSERT INTO @.T
> SELECT PflHierarchy.ChildPflID, PflHierarchy.ParentPflID, @.lvl
> FROM @.T AS T JOIN PflHierarchy
> ON T.lvl = @.lvl - 1
> AND PflHierarchy.ChildPflID = T.ParentPflID;
> END
> DELETE FROM @.T WHERE lvl = 0 OR ParentPflID IS NOT NULL;
> RETURN
> END
> I get the following error message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY
> constraint 'UQ__@.T__20A07422'. Cannot insert duplicate key in object
> '#1FAC4FE9'.
> My books by Joe Celko just came in, so I'll be reading them for more ideas
> to try.
> "Itzik Ben-Gan" wrote:
>