Pls, help: Bill of Materials and SQL 2005 recursion problem

/*
Hello everyone,
Thank you for taking your time to look at my problem. In our busy lives it's
hard to find time,
therefore, I really appreciate your effort.
The problem I am tryiing to resolve is this.
In the Bill of Materials structure, any idem can own components, therefore,
becoming an assembly.
Assemblies can own other assemblies. Any item can be used several times in t
he overall assembly.
However when manufacturing these assemblies, it's essential to know not only
that a part(assy) is owned by another part(assy),
but that any part(assy) is owned by a specific instance of the the parent.
In the example below, ScrewPaint is in the BOM two times and the screw is tw
o times, but the ScrewPaint on the row 11 belongs to screw instance 2.
and ScrewPaint on the row 7 of the result set, belongs to screw instance # o
ne.
The new SQL 2005 recursion is an excellent tool to process BOMs. What I try
to do is to calculate item instance and the instance of the parent within
the recursion algorithm. I am just not seeing how I can do it. I resorted to
path calculation and establishing it this way,
but it looks ugly and I don't like it.
I have make lots of futile efforts to somehow do it while recursing but my m
ind just does not see it. I am not a recursion expert yet.
Is there any way, using defined structure, to define the recursion in such a
way that I can get the final result,
i.e. calculate instance of an item and derive parent and instance on the fly
within recursion, without temp table and post-processing?
Thank you very much. Your time and knowledge is very appreciated.
Farmer.
*/
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
--, itmcQty
, itmID
-- , itmIDInstance
, P
, Level
) as
(
SELECT
i.itmID
, ic.CompitmID
-- , cast(null as smallint) as itmIDInstance
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
--, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY r.itmID ) as
smallint) as itmIDInstance
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
-- Capture the intermedeate result set
insert @.t
SELECT
CompitmID as ItmID
, cast(ROW_NUMBER() OVER ( PARTITION BY t.CompItmID ORDER BY t.CompItmID ) a
s smallint) as itmIDInstance
, itmID as itmIDParent
, SPACE(4*Level)+ CompitmID as CompTree
, P as Item
, NULLIF(left(P, len(p) -10),'') as ItemParent
, Level
-- ,(
-- SELECT itmIDInstance
-- FROM itm_tree t2
-- WHERE left(t2.P, len(t2.P) -10) = t.P
-- ) as itmIDInstance
FROM itm_tree t
ORDER BY p
-- result I am after
SELECT itmID, itmIDInstance, itmIDParent
,(
SELECT itmIDInstance
FROM @.t t2
WHERE t2.ItemPath = t.ItemPathParent
) as itmIDInstance
-- non- relevent columns
, Tree, ItemPath, ItemPathParent, [Level]
FROM @.t t
GO>"Farmer" <someone@.somewhere.com> wrote in message
>news:ugDbygkOGHA.1032@.TK2MSFTNGP11.phx.gbl...
>/*
>Hello everyone,
>Thank you for taking your time to look at my problem. In our busy lives
>it's hard to find time,
>therefore, I really appreciate your effort.
>The problem I am tryiing to resolve is this.
>In the Bill of Materials structure, any idem can own components, therefore,
>becoming an assembly.
>Assemblies can own other assemblies. Any item can be used several times in
>the overall assembly.
>However when manufacturing these assemblies, it's essential to know not
>only that a part(assy) is owned by another >part(assy),
>but that any part(assy) is owned by a specific instance of the the parent.
>In the example below, ScrewPaint is in the BOM two times and the screw is
>two times, but the ScrewPaint on the row >11 belongs to screw instance 2.
>and ScrewPaint on the row 7 of the result set, belongs to screw instance #
>one.
> . . .
How about
-- Define recursion
WITH itm_tree (
CompitmID
--, itmcQty
, itmID
-- , itmIDInstance
, P
, Level
, Instance
) as
(
SELECT
i.itmID
, ic.CompitmID
-- , cast(null as smallint) as itmIDInstance
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
, cast('0' as varchar(50))
FROM @.Items i
LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
--, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY
r.itmID ) as smallint) as itmIDInstance
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
, cast(r.Instance + '.' + cast(Row_Number() over (order by i.itmID)
as varchar) as varchar(50))
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
returns
ItmID itmIDInstance itmIDParent Instance CompTree
-- -- -- -- --
Cabinet 1 NULL 0 Cabinet
Case 1 Cabinet 0.1 Case
Door 1 Case 0.1.1 Door
SPiece 1 Door 0.1.1.1 SPiece
XPiece 1 Door 0.1.1.2 XPiece
Screw 1 Case 0.1.2 Screw
ScrewPaint 2 Screw 0.1.2.1 ScrewPaint
SideL 1 Case 0.1.3 SideL
SideR 1 Case 0.1.4 SideR
Screw 2 Cabinet 0.2 Screw
ScrewPaint 1 Screw 0.2.1 ScrewPaint
Which not only uniquely identifies each part, but also encodes where it's
located.
David|||Hi Farmer,
Interesting problem! I think the following does what you're looking for:
with T AS
(
SELECT
itmID,
compitmID,
1 as level
FROM @.itemcomponents
WHERE itmID = @.itmID
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
NULL AS itmID,
@.itmID AS compitmID,
0 AS level,
1 AS compitmNum,
1 AS itmNum
UNION ALL
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Farmer" <someone@.somewhere.com> wrote in message news:ugDbygkOGHA.1032@.TK2M
SFTNGP11.phx.gbl...
/*
Hello everyone,
Thank you for taking your time to look at my problem. In our busy lives it's
hard to find time,
therefore, I really appreciate your effort.
The problem I am tryiing to resolve is this.
In the Bill of Materials structure, any idem can own components, therefore,
becoming an assembly.
Assemblies can own other assemblies. Any item can be used several times in t
he overall assembly.
However when manufacturing these assemblies, it's essential to know not only
that a part(assy) is owned by another part(assy),
but that any part(assy) is owned by a specific instance of the the parent.
In the example below, ScrewPaint is in the BOM two times and the screw is tw
o times, but the ScrewPaint on the row 11 belongs to screw instance 2.
and ScrewPaint on the row 7 of the result set, belongs to screw instance # o
ne.
The new SQL 2005 recursion is an excellent tool to process BOMs. What I try
to do is to calculate item instance and the instance of the parent within
the recursion algorithm. I am just not seeing how I can do it. I resorted to
path calculation and establishing it this way,
but it looks ugly and I don't like it.
I have make lots of futile efforts to somehow do it while recursing but my m
ind just does not see it. I am not a recursion expert yet.
Is there any way, using defined structure, to define the recursion in such a
way that I can get the final result,
i.e. calculate instance of an item and derive parent and instance on the fly
within recursion, without temp table and post-processing?
Thank you very much. Your time and knowledge is very appreciated.
Farmer.
*/
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
--, itmcQty
, itmID
-- , itmIDInstance
, P
, Level
) as
(
SELECT
i.itmID
, ic.CompitmID
-- , cast(null as smallint) as itmIDInstance
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
--, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY r.itmID ) as
smallint) as itmIDInstance
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
-- Capture the intermedeate result set
insert @.t
SELECT
CompitmID as ItmID
, cast(ROW_NUMBER() OVER ( PARTITION BY t.CompItmID ORDER BY t.CompItmID ) a
s smallint) as itmIDInstance
, itmID as itmIDParent
, SPACE(4*Level)+ CompitmID as CompTree
, P as Item
, NULLIF(left(P, len(p) -10),'') as ItemParent
, Level
-- ,(
-- SELECT itmIDInstance
-- FROM itm_tree t2
-- WHERE left(t2.P, len(t2.P) -10) = t.P
-- ) as itmIDInstance
FROM itm_tree t
ORDER BY p
-- result I am after
SELECT itmID, itmIDInstance, itmIDParent
,(
SELECT itmIDInstance
FROM @.t t2
WHERE t2.ItemPath = t.ItemPathParent
) as itmIDInstance
-- non- relevent columns
, Tree, ItemPath, ItemPathParent, [Level]
FROM @.t t
GO|||Slightly more compact version:
WITH T AS
(
SELECT
CONVERT(varchar(10), NULL) AS itmID,
@.itmID AS compitmID,
0 as level
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message new
s:ed8TVplOGHA.2696@.TK2MSFTNGP14.phx.gbl...
Hi Farmer,
Interesting problem! I think the following does what you're looking for:
with T AS
(
SELECT
itmID,
compitmID,
1 as level
FROM @.itemcomponents
WHERE itmID = @.itmID
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
NULL AS itmID,
@.itmID AS compitmID,
0 AS level,
1 AS compitmNum,
1 AS itmNum
UNION ALL
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thank you very much Adam,
it does appeared to work as I sought it to work, but only at the begining...
Here is an updated code for my case.
Things i have learned from your code.
1. In the ancor part, the left join to components is useless (I don't know w
hy I didn't see it myself). I guess I am just too focused on my issue..., d
idn't see obvious.
2. that one can declare the second CTE and use in it the first one. I think
I read about it in the past, I just didn't think of using it. It replaces my
insert @.t temp table, i.e. making it virtual. Beautiful idea. I can also use
it in the subquery as well, i think.
3. I used ROW_RUMBER a number of times and used it in my original attempts b
ut I never considered RANK() function. It seems to be an original solution t
o use it, however it's faltering in the logic somehow. I adjusted my code wi
th your ideas. i decided to complicate it yet and added screws to more parts
making an assymbly "Screw" have more parents. That is where error came. For
example ScrewPaint instance should have a parent screw instance 1 but your
code makes it belong to instance 2 where as it belongs to instance 1 accordi
ng to the calculated path.
I am working on combination of 2 and my original code using calculated path.
I am not sure if your idea can still be taken advantage of. Do you see a wa
y?
In the nutshell here is a problem of converting BOM definition into a true t
ree, as very appropriately David Browny pointed out (prior post), encoding t
he tree. If each node where to be numbered, the component's parent IDs have
to point the right components.
Thank you for good ideas.
Vladimir Moldovanenko,
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','Screw', 1)
insert @.itemcomponents values ('XPiece','Screw', 1)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
, itmID
, itmcQty
, P
, Level
) as
(
SELECT
i.itmID
, cast(NULL as varchar(10)) as CompitmID
, cast(NULL as dec(18,4)) as itmcQty
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
-- LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
, ic.itmcQty
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
,T as (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS itmIDInstan
ce,
RANK() OVER (PARTITION BY itmID ORDER BY Level) AS itmIDParentInstance
FROM itm_tree
)
-- Capture the intermedeate result set
SELECT
CompitmID as ItmID
, itmIDInstance
, itmID as itmIDParent
, case when itmID is null then null else itmIDParentInstance end as itmIDPar
entInstance
--, itmcQty
, SPACE(4*Level)+ CompitmID as CompTree
, P as ItemPath
, Level
FROM t
ORDER BY p
Original code below
-- Define recursion
WITH itm_tree (
CompitmID
--, itmcQty
, itmID
-- , itmIDInstance
, P
, Level
) as
(
SELECT
i.itmID
, ic.CompitmID
-- , cast(null as smallint) as itmIDInstance
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
--, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY r.itmID ) as
smallint) as itmIDInstance
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
-- Capture the intermedeate result set
insert @.t
SELECT
CompitmID as ItmID
, cast(ROW_NUMBER() OVER ( PARTITION BY t.CompItmID ORDER BY t.CompItmID ) a
s smallint) as itmIDInstance
, itmID as itmIDParent
, SPACE(4*Level)+ CompitmID as CompTree
, P as Item
, NULLIF(left(P, len(p) -10),'') as ItemParent
, Level
-- ,(
-- SELECT itmIDInstance
-- FROM itm_tree t2
-- WHERE left(t2.P, len(t2.P) -10) = t.P
-- ) as itmIDInstance
FROM itm_tree t
ORDER BY p
-- result I am after
SELECT itmID, itmIDInstance, itmIDParent
,(
SELECT itmIDInstance
FROM @.t t2
WHERE t2.ItemPath = t.ItemPathParent
) as itmIDParentInstance
-- non- relevent columns
, Tree as CompTree, ItemPath, ItemPathParent, [Level]
FROM @.t t
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message new
s:%23qwjQ0lOGHA.1040@.TK2MSFTNGP12.phx.gbl...
Slightly more compact version:
WITH T AS
(
SELECT
CONVERT(varchar(10), NULL) AS itmID,
@.itmID AS compitmID,
0 as level
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message new
s:ed8TVplOGHA.2696@.TK2MSFTNGP14.phx.gbl...
Hi Farmer,
Interesting problem! I think the following does what you're looking for:
with T AS
(
SELECT
itmID,
compitmID,
1 as level
FROM @.itemcomponents
WHERE itmID = @.itmID
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
NULL AS itmID,
@.itmID AS compitmID,
0 AS level,
1 AS compitmNum,
1 AS itmNum
UNION ALL
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||For your better understanding this is a piece of acrual structure where data
will end up to be. there are more fields in it, but relevand are here.
itmID is not varchar, just for test clarity it was made.
CREATE TABLE [dbo].[OrderItems](
[itmID] [int] NOT NULL,
[itmIDInstance] [smallint] NOT NULL,
[itmIDParent] [int] NULL,
[itmIDParentInstance] [smallint] NULL,
..
[olnID] [int] NOT NULL,
[olnIDInstance] [smallint] NOT NULL,
..
CONSTRAINT [PK_OrderItems_itmID_itmIDInstance_olnID
_olnIDInstance] PRIMARY K
EY NONCLUSTERED
(
[itmID] ASC,
[itmIDInstance] ASC,
[olnID] ASC,
[olnIDInstance] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderItems] WITH CHECK ADD CONSTRAINT [FK_OrderItems_OrderItems]
FOREIGN KEY([itmIDParent], [itmIDParentInstance], [olnID], [olnIDInstance])
REFERENCES [dbo].[OrderItems] ([itmID], [itmIDInstance], [olnID], [olnIDInstance])
GO
"Farmer" <someone@.somewhere.com> wrote in message news:OSkZwxmOGHA.3896@.TK2M
SFTNGP15.phx.gbl...
Thank you very much Adam,
it does appeared to work as I sought it to work, but only at the begining...
Here is an updated code for my case.
Things i have learned from your code.
1. In the ancor part, the left join to components is useless (I don't know w
hy I didn't see it myself). I guess I am just too focused on my issue..., d
idn't see obvious.
2. that one can declare the second CTE and use in it the first one. I think
I read about it in the past, I just didn't think of using it. It replaces my
insert @.t temp table, i.e. making it virtual. Beautiful idea. I can also use
it in the subquery as well, i think.
3. I used ROW_RUMBER a number of times and used it in my original attempts b
ut I never considered RANK() function. It seems to be an original solution t
o use it, however it's faltering in the logic somehow. I adjusted my code wi
th your ideas. i decided to complicate it yet and added screws to more parts
making an assymbly "Screw" have more parents. That is where error came. For
example ScrewPaint instance should have a parent screw instance 1 but your
code makes it belong to instance 2 where as it belongs to instance 1 accordi
ng to the calculated path.
I am working on combination of 2 and my original code using calculated path.
I am not sure if your idea can still be taken advantage of. Do you see a wa
y?
In the nutshell here is a problem of converting BOM definition into a true t
ree, as very appropriately David Browny pointed out (prior post), encoding t
he tree. If each node where to be numbered, the component's parent IDs have
to point the right components.
Thank you for good ideas.
Vladimir Moldovanenko,
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','Screw', 1)
insert @.itemcomponents values ('XPiece','Screw', 1)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
, itmID
, itmcQty
, P
, Level
) as
(
SELECT
i.itmID
, cast(NULL as varchar(10)) as CompitmID
, cast(NULL as dec(18,4)) as itmcQty
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
-- LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
, ic.itmcQty
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
,T as (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS itmIDInstan
ce,
RANK() OVER (PARTITION BY itmID ORDER BY Level) AS itmIDParentInstance
FROM itm_tree
)
-- Capture the intermedeate result set
SELECT
CompitmID as ItmID
, itmIDInstance
, itmID as itmIDParent
, case when itmID is null then null else itmIDParentInstance end as itmIDPar
entInstance
--, itmcQty
, SPACE(4*Level)+ CompitmID as CompTree
, P as ItemPath
, Level
FROM t
ORDER BY p
Original code below
-- Define recursion
WITH itm_tree (
CompitmID
--, itmcQty
, itmID
-- , itmIDInstance
, P
, Level
) as
(
SELECT
i.itmID
, ic.CompitmID
-- , cast(null as smallint) as itmIDInstance
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
--, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY r.itmID ) as
smallint) as itmIDInstance
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
-- Capture the intermedeate result set
insert @.t
SELECT
CompitmID as ItmID
, cast(ROW_NUMBER() OVER ( PARTITION BY t.CompItmID ORDER BY t.CompItmID ) a
s smallint) as itmIDInstance
, itmID as itmIDParent
, SPACE(4*Level)+ CompitmID as CompTree
, P as Item
, NULLIF(left(P, len(p) -10),'') as ItemParent
, Level
-- ,(
-- SELECT itmIDInstance
-- FROM itm_tree t2
-- WHERE left(t2.P, len(t2.P) -10) = t.P
-- ) as itmIDInstance
FROM itm_tree t
ORDER BY p
-- result I am after
SELECT itmID, itmIDInstance, itmIDParent
,(
SELECT itmIDInstance
FROM @.t t2
WHERE t2.ItemPath = t.ItemPathParent
) as itmIDParentInstance
-- non- relevent columns
, Tree as CompTree, ItemPath, ItemPathParent, [Level]
FROM @.t t
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message new
s:%23qwjQ0lOGHA.1040@.TK2MSFTNGP12.phx.gbl...
Slightly more compact version:
WITH T AS
(
SELECT
CONVERT(varchar(10), NULL) AS itmID,
@.itmID AS compitmID,
0 as level
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message new
s:ed8TVplOGHA.2696@.TK2MSFTNGP14.phx.gbl...
Hi Farmer,
Interesting problem! I think the following does what you're looking for:
with T AS
(
SELECT
itmID,
compitmID,
1 as level
FROM @.itemcomponents
WHERE itmID = @.itmID
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
NULL AS itmID,
@.itmID AS compitmID,
0 AS level,
1 AS compitmNum,
1 AS itmNum
UNION ALL
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||thank you so much David,
please see my other replies to Adam's posts. Your idea is along the idea of
calculated path I have used, it's just more compressed encoded path
calculation. I like it, it's neater. This data will end up in this table. I
will try to work on incorporating your idea and dual CTE declaration
suggested by Adam and I will post the results.
Thanks again for your idea.
For your better understanding this is a piece of actual structure where data
will end up to be. there are more fields in it, but relevand are here.
itmID is not varchar, just for test clarity it was made.
CREATE TABLE [dbo].[OrderItems](
[itmID] [int] NOT NULL,
[itmIDInstance] [smallint] NOT NULL,
[itmIDParent] [int] NULL,
[itmIDParentInstance] [smallint] NULL,
..
[olnID] [int] NOT NULL,
[olnIDInstance] [smallint] NOT NULL,
..
CONSTRAINT [PK_OrderItems_itmID_itmIDInstance_olnID
_olnIDInstance] PRIMARY
KEY NONCLUSTERED
(
[itmID] ASC,
[itmIDInstance] ASC,
[olnID] ASC,
[olnIDInstance] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderItems] WITH CHECK ADD CONSTRAINT
[FK_OrderItems_OrderItems]
FOREIGN KEY([itmIDParent], [itmIDParentInstance], [olnID], [olnIDInstance])
REFERENCES [dbo].[OrderItems] ([itmID], [itmIDInstance], [olnID],
[olnIDInstance])
GO
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OU%23dUolOGHA.3856@.TK2MSFTNGP12.phx.gbl...
>
> How about
>
> -- Define recursion
> WITH itm_tree (
> CompitmID
> --, itmcQty
> , itmID
> -- , itmIDInstance
> , P
> , Level
> , Instance
> ) as
> (
> SELECT
> i.itmID
> , ic.CompitmID
> -- , cast(null as smallint) as itmIDInstance
> , CONVERT(varchar(max), cast(i.itmID as char(10))) as P
> , 1 as Level
> , cast('0' as varchar(50))
> FROM @.Items i
> LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
> WHERE i.itmID = @.itmID
> UNION ALL
> SELECT
> ic.CompitmID
> , ic.itmID
> --, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY
> r.itmID ) as smallint) as itmIDInstance
> , CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
> , r.Level + 1 as Level
> , cast(r.Instance + '.' + cast(Row_Number() over (order by
> i.itmID) as varchar) as varchar(50))
> FROM @.Items i
> JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
> JOIN itm_tree r ON r.CompitmID = ic.itmID
> )
>
> returns
> ItmID itmIDInstance itmIDParent Instance CompTree
> -- -- -- -- --
-
> Cabinet 1 NULL 0 Cabinet
> Case 1 Cabinet 0.1 Case
> Door 1 Case 0.1.1 Door
> SPiece 1 Door 0.1.1.1 SPiece
> XPiece 1 Door 0.1.1.2 XPiece
> Screw 1 Case 0.1.2 Screw
> ScrewPaint 2 Screw 0.1.2.1
> ScrewPaint
> SideL 1 Case 0.1.3 SideL
> SideR 1 Case 0.1.4 SideR
> Screw 2 Cabinet 0.2 Screw
> ScrewPaint 1 Screw 0.2.1 ScrewPaint
>
> Which not only uniquely identifies each part, but also encodes where it's
> located.
> David
>
>|||Adam and David,
thank you for your help. I think I got it.
This is what I got so far.
any better ideas? David, i used my path calculation as in my db, I have itmI
D as integer. Using str(itmID, 10) to encode is making it easier to chop the
last (delimited by position) path element.
With your calculation, I need to search for a position of the past "." in th
e string. This may require an iterative function. ? Therefore I thought th
at WHERE s2.P = left(s1.P, len(s1.P) - 10) is simpler.
You may ask me why did I go with the structure I poasted. Well, my thinking
was that int+smallint is much smaller than varchar(max) ? for FK reference a
nd usage even if it is multi-key. For me it just made sense.
Thanks again. You are both very sharp.
any optimizations you see?
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','Screw', 1)
insert @.itemcomponents values ('XPiece','Screw', 1)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
, itmID
, itmcQty
, P
, Level
, P2
) as
(
SELECT
i.itmID
, cast(NULL as varchar(10)) as CompitmID
, cast(NULL as dec(18,4)) as itmcQty
, convert(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
, cast('0' as varchar(max)) P2
FROM @.Items i
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
, ic.itmcQty
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
, cast(r.P2 + '.' + cast(ROW_NUMBER() OVER (ORDER BY i.itmID) as varchar) a
s varchar(max))
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
),tmp AS
(
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY itm_tree.CompitmID ORDER BY itm_tree.Compit
mID) AS itmIDInstance
FROM itm_tree
)
SELECT
CompitmID as ItmID
, itmIDInstance
, itmID as itmIDParent
,(
SELECT itmIDInstance
FROM tmp s2
WHERE s2.P = left(s1.P, len(s1.P) - 10)
) as itmIDParentInstance
--, itmcQty
, SPACE(4*Level)+ CompitmID as CompTree
, P2
, P as ItemPath
, Level
FROM tmp s1
ORDER BY p2|||But then again. Know thy functions. David, your idea can also work.
It just appers that left(s1.P, len(s1.P) - 10) is simpler than
REVERSE(P2) once, then REVERSE(case when CHARINDEX( '.', RT) > 0 then substr
ing(RT, CHARINDEX( '.', RT) + 1, LEN(RT) - CHARINDEX( '.', RT)) end) to acco
mplish the same thing.
thanks
Vladimir
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','Screw', 1)
insert @.itemcomponents values ('XPiece','Screw', 1)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
, itmID
, itmcQty
, P
, Level
, P2
) as
(
SELECT
i.itmID
, cast(NULL as varchar(10)) as CompitmID
, cast(NULL as dec(18,4)) as itmcQty
, convert(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
, cast('0' as varchar(max)) P2
FROM @.Items i
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
, ic.itmcQty
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
, cast(r.P2 + '.' + cast(ROW_NUMBER() OVER (ORDER BY i.itmID) as varchar) a
s varchar(max))
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
),tmp AS
(
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY itm_tree.CompitmID ORDER BY itm_tree.Compit
mID) AS itmIDInstance
,REVERSE(P2) as RT
FROM itm_tree
)
SELECT
CompitmID as ItmID
, itmIDInstance
, itmID as itmIDParent
,(
SELECT itmIDInstance
FROM tmp s2
WHERE s2.P = left(s1.P, len(s1.P) - 10)
) as itmIDParentInstance
--, itmcQty
, SPACE(4*Level)+ CompitmID as CompTree
, P2 Node
, REVERSE(case when CHARINDEX( '.', RT) > 0 then substring(RT, CHARINDEX( '.
', RT) + 1, LEN(RT) - CHARINDEX( '.', RT)) end) ParentNode
, P as ItemPath
, Level
FROM tmp s1
ORDER BY p2|||Get a copy of TREES & HIERATCHIES IN SQL and use the chapter on BOM.
You do not need recursion at all and your queries will run 1-2 orders
of magnitude faster.