May 26, 2010 at 4:32 am
Hi. Firtsly aplogies for not posting table defs and sample code, I am unsure how to get a sample set of data for my request.
I am trying to write code for a report to list a Bil Of Materials.
The BOM contains components and other BOM's (Sub Assemblies).
I do not know how to explode the BOM right down.
The code below lists the Parent Product and the Components 1 level down.
DECLARE @ProductId NVARCHAR(30)
SET @ProductId = '5-ZSA0020X';
WITH ProductComponents AS
(
SELECT
p.Product,
p.ProductId AS ParentProductId,
p.ProductDescription AS ParentDescription,
psv.Available AS ProdAvailable,
c.ProductId AS ComponentProductId,
c.ProductDescription AS ComponentDescription,
csv.Available AS CompAvailable,
s.Quantity AS ComponentQuantity
FROM Structures AS s
INNER JOIN Products AS p ON s.Product = p.Product
INNER JOIN Products AS c ON s.Component = c.Product
INNER JOIN StructureVersions AS psv ON s.StructureVersion = psv.StructureVersion AND psv.Available = 1
INNER JOIN StructureVersions AS csv ON s.StructureVersion = csv.StructureVersion AND csv.Available = 1
)
SELECT *
FROM ProductComponents AS pc
WHERE pc.ParentProductId = @ProductId
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 26, 2010 at 5:13 am
Hi. I am attempting to put together from sample data as I know it helps.
Watch this space.
Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 26, 2010 at 5:35 am
This was removed by the editor as SPAM
May 26, 2010 at 5:38 am
Hi (still working on sample code).
I ran your code and received error:
Msg 253, Level 16, State 1, Line 4
Recursive member of a common table expression 'ProductComponents' has multiple recursive references.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 26, 2010 at 5:52 am
This was removed by the editor as SPAM
May 26, 2010 at 9:00 am
I have attached a sample script that creates the required tables (tempdb) and inserts 5000 rows.
I have modified my original query to run against the sample tables.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 26, 2010 at 9:06 am
Phil,
Thanks for the table defs and sample data. I am looking into it. In the meantime - has the revised query that was already provided been of use?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 26, 2010 at 9:08 am
Your script is one huge single line of text!,
Could you please try to make it a bit more usable.
Thanks.
May 26, 2010 at 9:15 am
I feel is is usable? Click execute and all is revealed........It is the preferred way to post code on the forum.
I was having a few problems getting the updated query to run....will look again.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 26, 2010 at 9:20 am
The updated query returns:
Msg 207, Level 16, State 1, Line 28
Invalid column name 'ProductId'.
Msg 207, Level 16, State 1, Line 29
Invalid column name 'ProductDescription'
Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 26, 2010 at 9:39 am
This version of recursive CTE is based on assumption that the Structures.Component is a parent of other parts (products).
WITH ProductComponents AS
(
SELECT
p.Product,
p.ProductId AS ParentProductId,
c.ProductId AS ComponentProductId,
s.Quantity AS ComponentQuantity
FROM Structures AS s
INNER JOIN Products AS p ON s.Product = p.Product
INNER JOIN Products AS c ON s.Component = c.Product
INNER JOIN StructureVersions AS psv ON s.StructureVersion = psv.StructureVersion --AND psv.Available = 1
INNER JOIN StructureVersions AS csv ON s.StructureVersion = csv.StructureVersion --AND csv.Available = 1
UNION ALL
SELECT
p.Product,
p.ProductId AS ParentProductId,
c.ProductId AS ComponentProductId,
s.Quantity AS ComponentQuantity
FROM Structures AS s
INNER JOIN Products AS p ON s.Product = p.Product
INNER JOIN Products AS c ON s.Component = c.Product
INNER JOIN StructureVersions AS psv ON s.StructureVersion = psv.StructureVersion --AND psv.Available = 1
INNER JOIN StructureVersions AS csv ON s.StructureVersion = csv.StructureVersion --AND csv.Available = 1
INNER JOIN ProductComponents pc on pc.Product = s.Component
)
Which columns in your tables define recursive relationship?
May 26, 2010 at 1:13 pm
Hi. Thanks for your efforts however the output is not as expected. The query returned 2304 rows for a single parent product and took 1 minute 46 seconds to execute.
Regards,
Phil.
Update: Doing some manual work against the production data I expected 31 rows for the product
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 26, 2010 at 5:17 pm
It is very easy to write query which returns requied number of records, just add TOP #OfRequiredRecords straight after SELECT keyword 😀
Seriously, it's not clear from your table structure what defines recursive realtionship in your entities. So, again: Which columns in your tables define recursive relationship?
For example let say I have a table Employee
and it has the following columns:
EmployeeID
ManagerID
Name
Phone
etc.
In the above table the recursive realtionship is defined as
ManagerID 1-00 EmployeeID (one-to-many)
Based on this information you can build the query which will return all employees who report to the given manager including employees reporting to employees who report to this manager etc.
Isn't the similar thing you want to achieve?
My current understanding of your structure is:
You have a product which consists of some parts, which, in the own case, consist of another parts etc. And you need the query which will return all parts, sub-parts, sub-sub-parts and etc of the given product. Am I right? To help you with this, understanding of how recursive relationship defined is required.
Cheers
May 27, 2010 at 3:24 am
The structures table has a Product and Component. The component itself is a product and may also contain other products.
So.
Product A has components B,C,D,E,F
B
.....D
.....E
C
D
.....F
E
F
B also contains D & E
D also contains F.
When user searches for BOM they enter the top level productId from products table.
Related records in the structures table should then be displayed.
Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
May 27, 2010 at 4:42 am
Try this:
DECLARE @ProductId NVARCHAR(30)
SET @ProductId = 'AZXXX423AA';
WITH ProductComponents AS
(
SELECT
s.Product,
s.Component,
s.Quantity
FROM Structures AS s
INNER JOIN Products AS p ON s.Product = p.Product
WHERE p.ProductId = @ProductId
UNION ALL
SELECT
s.Product,
s.Component,
s.Quantity
FROM Structures AS s
INNER JOIN ProductComponents AS c ON c.Component = s.Product
)
SELECT pc.Product
,pc.Component
,p.ProductId AS ParentProductId
,c.ProductId AS ComponentProductId
,pc.Quantity AS ComponentQuantity
FROM ProductComponents pc
JOIN Products AS p ON pc.Product = p.Product
JOIN Products AS c ON pc.Component = c.Product
You can also join to the StructureVersions table as required.
I am not sure why in your query you joined to StructureVersions table twice on the same column:
INNER JOIN StructureVersions AS psv ON s.StructureVersion = psv.StructureVersion --AND psv.Available = 1
INNER JOIN StructureVersions AS csv ON s.StructureVersion = csv.StructureVersion --AND csv.Available = 1
From what I can see, based on your test data, the above CTE returns what you wanted. If not, please provide the expected results for the given test data.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply