jsnosal - Tuesday, January 22, 2013 10:56 AM:
Has anyone created a SQL query to create a multi level BOM report or is there documentation on this? I've looked at the SQL reporting documentation, I just haven't seen anything for grabbing the all the parts assoisated with all assemblies. Thanks for your help
aponceot - Tuesday, January 22, 2013 11:29 AM:
Hi,
You can have a look to CTE queries (msdn.microsoft.com/.../ms186243(v=sql.105).aspx)
Something like :
WITH RecursiveBOM(source_id,related_id,indent_level) AS
(
SELECT SOURCE_ID,RELATED_ID, 0
FROM innovator.PART_BOM pb
WHERE SOURCE_ID = '@id'
UNION ALL
SELECT pb.SOURCE_ID,pb.RELATED_ID, indent_level +1
FROM innovator.PART_BOM pb
INNER JOIN RecursiveBOM rb ON rb.related_id = pb.SOURCE_ID
)
SELECT sp.ITEM_NUMBER,rp.ITEM_NUMBER,rp.WEIGHT,indent_level
FROM RecursiveBOM rb
INNER JOIN innovator.PART sp ON rb.source_id = sp.ID
INNER JOIN innovator.PART rp ON rb.related_id = rp.ID
jsnosal - Tuesday, January 22, 2013 12:17 PM:
Thank you so much this is perfect. Do you have any recommendations for including the AML list or documents?
jsnosal - Tuesday, January 22, 2013 4:12 PM:
I'm still trying to fully understand the recursive query. I'm new to SQL so I'm learning as I go. I'm not sure if I'm doing something wrong but I'm only seeing down to the second level on the SQL query that was posted above. Any ideas why I'm only seeing two indent levels?
The query below still needs work, but its what I'm looking to do. Should I be creating more recursive queries for each JOIN that I have below or should I be using a different statement? Thank you for your help
WITH RecursiveBOM(source_id,related_id,indent_level) AS
(
SELECT SOURCE_ID,RELATED_ID, 0
FROM innovator.PART_BOM pbom
WHERE SOURCE_ID = '@'
UNION ALL
SELECT pbom.SOURCE_ID,pbom.RELATED_ID, indent_level +1
FROM innovator.PART_BOM pbom
INNER JOIN RecursiveBOM rbom ON rbom.related_id = pbom.SOURCE_ID
)
SELECT
indent_level,
sp.ITEM_NUMBER,
sp.NAME,
rp.ITEM_NUMBER,
rp.NAME,
[innovator].[PART_BOM].[REFERENCE_DESIGNATOR],
[innovator].[PART_BOM].[QUANTITY],
[innovator].[MANUFACTURER_PART].[ITEM_NUMBER],
[innovator].[MANUFACTURER].[NAME],
[innovator].[MANUFACTURER_PART].[STATE],
[innovator].[DOCUMENT].[ITEM_NUMBER],
[innovator].[DOCUMENT].[NAME],
[innovator].[DOCUMENT].[MAJOR_REV]
FROM RecursiveBOM rb
INNER JOIN innovator.PART sp
ON rb.source_id = sp.ID
INNER JOIN innovator.PART rp
ON rb.related_id = rp.ID
LEFT JOIN innovator.PART_BOM
on innovator.PART_BOM.SOURCE_ID = sp.ID
LEFT JOIN innovator.PART_AML
on innovator.PART_AML.SOURCE_ID = rp.ID
LEFT JOIN innovator.PART_DOCUMENT
on innovator.PART_DOCUMENT.SOURCE_ID = sp.ID
LEFT JOIN innovator.DOCUMENT
on innovator.DOCUMENT.ID = innovator.PART_DOCUMENT.RELATED_ID
LEFT JOIN innovator.MANUFACTURER_PART
on innovator.MANUFACTURER_PART.ID = innovator.PART_AML.RELATED_ID
LEFT JOIN innovator.MANUFACTURER
on innovator.MANUFACTURER.ID = innovator.MANUFACTURER_PART.MANUFACTURER
