May 25, 2011 at 4:33 am
I am trying to create a function that returns a bill of materials for a given product. The problem is difficult to explain so I've included some test data etc. Basically, I have a product which only has one component but this product is also a component of other products. The product can be sold by itself or included within its parent products. If I run the query for the specific product incorrect results are returned, instead of one row being returned (the correct result) I get 4:
--Create temp table for example
CREATE TABLE #ProductAssembly
(
product nchar(20),
component nchar(20)
)
--Insert some test data
INSERT INTO #ProductAssembly
SELECT 'Part A','Part Z'
UNION ALL
SELECT 'Part B','Part Z'
UNION ALL
SELECT 'Part C','Part Z'
UNION ALL
SELECT 'Part D','Part Z'
UNION ALL
SELECT 'Part Z','Part Y'
;WITH Structure (product, component, structure_level)
AS
(
/* Define anchor members i.e. those products that
** are not themselves components of other products
*/
SELECT parent_parts.product,
parts.component,
0 AS structure_level
FROM (
SELECT product FROM #ProductAssembly (NOLOCK)
EXCEPT
SELECT component FROM #ProductAssembly (NOLOCK)
) parent_parts
JOIN #ProductAssembly parts (NOLOCK)
ON parts.product = parent_parts.product
UNION ALL
/* Join the temp table back on to itself where the
** the component is equal to the parent product
*/
SELECT ps.product,
ps.component,
s.structure_level + 1
FROM dbo.#ProductAssembly ps (NOLOCK)
JOIN Structure s
ON s.component = ps.product
)
SELECT *
FROM Structure
WHERE product = 'Part Z'
DROP TABLE #ProductAssembly
May 25, 2011 at 4:54 am
Please state expected results for
SELECT *
FROM Structure
WHERE product = 'Part A'
Would you want to see Part Y in the results?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 25, 2011 at 5:06 am
Phil Parkin (5/25/2011)
Please state expected results for
SELECT *FROM Structure
WHERE product = 'Part A'
Would you want to see Part Y in the results?
Yes, I would need to see 'Part Y' and not 'Part Z' as the component because the bill of materials is only concerned with the bottom level parts.
May 25, 2011 at 10:35 am
I took a shot at this one...see if this one works for you:
IF OBJECT_ID(N'tempdb..#ProductAssembly ') > 0
DROP TABLE #ProductAssembly ;
GO
--Create temp table for example
CREATE TABLE #ProductAssembly
(
product nchar(20),
component nchar(20)
)
--Insert some test data
INSERT INTO #ProductAssembly
SELECT 'Part A','Part Z'
UNION ALL
SELECT 'Part B','Part Z'
UNION ALL
SELECT 'Part C','Part Z'
UNION ALL
SELECT 'Part D','Part Z'
UNION ALL
SELECT 'Part Z','Part Y' ;
WITH Structure(product, component, structure_level)
AS (
/* Define anchor members i.e. those products that
** are not themselves components of other products
*/
SELECT parent_parts.product,
parent_parts.product,
0 AS structure_level
FROM #ProductAssembly parent_parts
UNION ALL
/* Join the temp table back on to itself where the
** the component is equal to the parent product
*/
SELECT s.product,
ps.component,
s.structure_level + 1
FROM dbo.#ProductAssembly ps
JOIN Structure s ON ps.product = s.component
)
SELECT s.product,
s.component,
s.structure_level
FROM Structure s
WHERE s.structure_level > 0
AND s.product = 'Part A'
AND s.structure_level = (
SELECT MAX(structure_level)
FROM Structure
WHERE product = s.product
)
ORDER BY s.structure_level,
s.product,
s.component ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 26, 2011 at 7:00 am
Thanks, it works using the sample data but not on my production data....digging around now to try and figure out why it doesn't!
May 26, 2011 at 7:33 am
Are you getting an error by chance or just wrong results? If you post some sample data that demonstrates why it is not working I'll try to take another look at it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 26, 2011 at 8:33 am
To reproduce the error (or rather incorrect results) I've added some more items to the sample table:
IF OBJECT_ID(N'tempdb..#ProductAssembly ') > 0
DROP TABLE #ProductAssembly ;
GO
--Create temp table for example
CREATE TABLE #ProductAssembly
(
product nchar(20),
component nchar(20)
)
--Insert some test data
INSERT INTO #ProductAssembly
SELECT 'Part A','Part Z'
UNION ALL
SELECT 'Part B','Part Z'
UNION ALL
SELECT 'Part C','Part Z'
UNION ALL
SELECT 'Part D','Part Z'
UNION ALL
SELECT 'Part Z','Part Y'
UNION ALL
SELECT 'Part D','Part X'
UNION ALL
SELECT 'Part D','Part W';
WITH Structure(product, component, structure_level)
AS (
/* Define anchor members i.e. those products that
** are not themselves components of other products
*/
SELECT parent_parts.product,
parent_parts.product,
0 AS structure_level
FROM #ProductAssembly parent_parts
UNION ALL
/* Join the temp table back on to itself where the
** the component is equal to the parent product
*/
SELECT s.product,
ps.component,
s.structure_level + 1
FROM dbo.#ProductAssembly ps
JOIN Structure s ON ps.product = s.component
)
SELECT s.product,
s.component,
s.structure_level
FROM Structure s
WHERE s.structure_level > 0
AND s.product = 'Part D'
AND s.structure_level = (
SELECT MAX(structure_level)
FROM Structure
WHERE product = s.product
)
ORDER BY s.structure_level,
s.product,
s.component ;
May 26, 2011 at 8:49 am
Just to clarify, the error is not the duplication (resolved using DISTINCT), it is that only the very last level in the structure is returned even though the Part D has components at different levels.
May 26, 2011 at 9:04 am
What is the expected result from this new set of test data please?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 26, 2011 at 9:08 am
Using "Part D" as the example the result would be the following components:
Part X
Part W
Part Y
May 26, 2011 at 10:21 am
OK, had to add another layer in the form of a iTVF and then use CROSS APPLY. See if this setup works for you:
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.get_leaf') )
DROP FUNCTION dbo.get_leaf ;
GO
CREATE FUNCTION dbo.get_leaf
(
@product NCHAR(20),
@component NCHAR(20)
)
RETURNS TABLE
AS
RETURN
(
WITH Structure(top_level_product, product, component, structure_level)
AS (
/* Define anchor members i.e. those products that
** are not themselves components of other products
*/
SELECT DISTINCT
p1.product,
p1.product,
p1.component,
0 AS structure_level
FROM dbo.ProductAssembly p1
WHERE p1.product = @product
AND p1.component = @component
UNION ALL
/* Join the temp table back on to itself where the
** the component is equal to the parent product
*/
SELECT s.top_level_product,
ps.product,
ps.component,
s.structure_level + 1
FROM Structure s
JOIN dbo.ProductAssembly ps ON s.component = ps.product
)
SELECT TOP 1
top_level_product,
component
FROM Structure
ORDER BY structure_level DESC ) ;
GO
IF OBJECT_ID(N'dbo.ProductAssembly ') > 0
DROP TABLE dbo.ProductAssembly ;
GO
--Create temp table for example
CREATE TABLE dbo.ProductAssembly
(
product NCHAR(20),
component NCHAR(20)
)
--Insert some test data
INSERT INTO dbo.ProductAssembly
SELECT 'Part A','Part Z'
UNION ALL
SELECT 'Part B','Part Z'
UNION ALL
SELECT 'Part C','Part Z'
UNION ALL
SELECT 'Part D','Part Z'
UNION ALL
SELECT 'Part Z','Part Y'
UNION ALL
SELECT 'Part D','Part X'
UNION ALL
SELECT 'Part D','Part W';
GO
SELECT DISTINCT
gl.top_level_product,
gl.component
FROM dbo.ProductAssembly pa
CROSS APPLY dbo.get_leaf(pa.product, pa.component) gl
WHERE pa.product = 'Part D' ;
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 29, 2011 at 3:52 am
Well, initial testing on my production data looks good! So thank you very much for your help with this problem 😀
May 29, 2011 at 7:13 am
Excellent, happy to assist 🙂 Thanks for posting back!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 29, 2011 at 10:30 am
David-155102 (5/26/2011)
Using "Part D" as the example the result would be the following components:Part X
Part W
Part Y
Gosh... what would you want if Part Z had more than just Part Y as a component?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2011 at 4:43 am
Jeff Moden (5/29/2011)
David-155102 (5/26/2011)
Using "Part D" as the example the result would be the following components:Part X
Part W
Part Y
Gosh... what would you want if Part Z had more than just Part Y as a component?
Hi Jeff,
I see your point; if I change the test data to the following then "Part G" is not returned when it should be :crazy::
--Insert some test data
INSERT INTO dbo.ProductAssembly
SELECT 'Part A','Part Z'
UNION ALL
SELECT 'Part B','Part Z'
UNION ALL
SELECT 'Part C','Part Z'
UNION ALL
SELECT 'Part D','Part Z'
UNION ALL
SELECT 'Part Z','Part Y'
UNION ALL
SELECT 'Part D','Part X'
UNION ALL
SELECT 'Part D','Part W'
UNION ALL
SELECT 'Part Z','Part G';
GO
SELECT DISTINCT
gl.top_level_product,
gl.component
FROM dbo.ProductAssembly pa
CROSS APPLY dbo.get_leaf(pa.product, pa.component) gl
WHERE pa.product = 'Part A' ;
GO
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply