• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

SQL Query Problem

I have the following code which returns all the component parts from the BomStructure table for the StockCode entered.
Is it possible to re-write to enable a component part to be entered and the code only return the toplevelpart?

SELECT     TOP 100 PERCENT X.StockCode AS topLevelPart, X.PartCategory, COALESCE (E.Component, D.Component, C.Component, B.Component, A.Component) 
                      AS lowerLevelPart
FROM         (SELECT     InvMaster.StockCode AS StockCode, InvMaster.PartCategory AS PartCategory
                       FROM          InvMaster
                       GROUP BY InvMaster.StockCode, InvMaster.PartCategory) X INNER JOIN
                      dbo.BomStructure A ON A.ParentPart = X.StockCode LEFT OUTER JOIN
                      dbo.BomStructure B ON B.ParentPart = A.Component LEFT OUTER JOIN
                      dbo.BomStructure C ON C.ParentPart = B.Component LEFT OUTER JOIN
                      dbo.BomStructure D ON D.ParentPart = C.Component LEFT OUTER JOIN
                      dbo.BomStructure E ON E.ParentPart = D.Component
WHERE     (X.StockCode = 'X323213H6XW001')
ORDER BY COALESCE (E.Component, D.Component, C.Component, B.Component, A.Component), X.StockCode

Open in new window

0
RickCooper
Asked:
RickCooper
  • 7
  • 6
1 Solution
 
vinurajrCommented:
which table contains the component part..? and what do you mean by code only return the toplevelpart..?

can you give some sample..
0
 
RickCooperAuthor Commented:
vinurair,

 The component part is in the BomStructure table

e.g.

The TopLevelPart (X323213H6XW001) is held on the InvMaster Table as StockCode.
This part could be made of a number of components These are held on the BomStructure Table.

BomStructure Table
ParentPart                  Component
X323213H6XW001      X123546
X323213H6XW001      X568974
X323213H6XW001      D45367
X123546                      PD34525
X123546                      CH6780

If I ran the original code for X323213H6XW001 It would return
ToplevelPart                LowerLevelPart
X323213H6XW001      X123546
X323213H6XW001      X568974
X323213H6XW001      D45367
X323213H6XW001      PD34525
X323213H6XW001      CH6780

Part X123546 is used in the TopLevel but is made up from PD34525 and CH6780

I would like to be able to enter any of the LowerLevel Parts and have the TopLevelPart returned.


0
 
kemi67Commented:
Perhaps something like this?
SELECT DISTINCT Z.topLevelPart FROM
(
SELECT     TOP 100 PERCENT X.StockCode AS topLevelPart, X.PartCategory, COALESCE (E.Component, D.Component, C.Component, B.Component, A.Component) 
                      AS lowerLevelPart
FROM         (SELECT     InvMaster.StockCode AS StockCode, InvMaster.PartCategory AS PartCategory
                       FROM          InvMaster
                       GROUP BY InvMaster.StockCode, InvMaster.PartCategory) X INNER JOIN
                      dbo.BomStructure A ON A.ParentPart = X.StockCode LEFT OUTER JOIN
                      dbo.BomStructure B ON B.ParentPart = A.Component LEFT OUTER JOIN
                      dbo.BomStructure C ON C.ParentPart = B.Component LEFT OUTER JOIN
                      dbo.BomStructure D ON D.ParentPart = C.Component LEFT OUTER JOIN
                      dbo.BomStructure E ON E.ParentPart = D.Component
ORDER BY COALESCE (E.Component, D.Component, C.Component, B.Component, A.Component), X.StockCode
) AS Z 
where Z.LowerPart='InsertHereComponentCode'

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
RickCooperAuthor Commented:
Kemi67,

 I have tried you code and it returns no top level part when I enter a lowerlevel
0
 
kemi67Commented:
I've written LowerPart instead of lowerLevelPart.
Try this one
SELECT DISTINCT Z.topLevelPart FROM
(
SELECT     TOP 100 PERCENT X.StockCode AS topLevelPart, X.PartCategory, COALESCE (E.Component, D.Component, C.Component, B.Component, A.Component) 
                      AS lowerLevelPart
FROM         (SELECT     InvMaster.StockCode AS StockCode, InvMaster.PartCategory AS PartCategory
                       FROM          InvMaster
                       GROUP BY InvMaster.StockCode, InvMaster.PartCategory) X INNER JOIN
                      dbo.BomStructure A ON A.ParentPart = X.StockCode LEFT OUTER JOIN
                      dbo.BomStructure B ON B.ParentPart = A.Component LEFT OUTER JOIN
                      dbo.BomStructure C ON C.ParentPart = B.Component LEFT OUTER JOIN
                      dbo.BomStructure D ON D.ParentPart = C.Component LEFT OUTER JOIN
                      dbo.BomStructure E ON E.ParentPart = D.Component
ORDER BY COALESCE (E.Component, D.Component, C.Component, B.Component, A.Component), X.StockCode
) AS Z 
where Z.lowerLevelPart='InsertHereComponentCode'

Open in new window

0
 
RickCooperAuthor Commented:
Hi,

 I changed the LowerPart to lowerLevelPart when I originally tested and it did not return any parts.
0
 
kemi67Commented:
Following your example, which lower code did you use?
0
 
RickCooperAuthor Commented:
X568974
0
 
kemi67Commented:
Did you have no result or error?
Can you run the query without the WHERE clause and tell what happens?
0
 
RickCooperAuthor Commented:
I have run this code:
SELECT DISTINCT Z.topLevelPart, Z.lowerLevelPart FROM
(
SELECT     TOP 100 PERCENT X.StockCode AS topLevelPart, X.PartCategory, COALESCE (E.Component, D.Component, C.Component, B.Component, A.Component) 
                      AS lowerLevelPart
FROM         (SELECT     InvMaster.StockCode AS StockCode, InvMaster.PartCategory AS PartCategory
                       FROM          InvMaster
                       GROUP BY InvMaster.StockCode, InvMaster.PartCategory) X INNER JOIN
                      dbo.BomStructure A ON A.ParentPart = X.StockCode LEFT OUTER JOIN
                      dbo.BomStructure B ON B.ParentPart = A.Component LEFT OUTER JOIN
                      dbo.BomStructure C ON C.ParentPart = B.Component LEFT OUTER JOIN
                      dbo.BomStructure D ON D.ParentPart = C.Component LEFT OUTER JOIN
                      dbo.BomStructure E ON E.ParentPart = D.Component
ORDER BY COALESCE (E.Component, D.Component, C.Component, B.Component, A.Component), X.StockCode
) AS Z 

Open in new window


If the lowerLevelPart is on the first level of the bom it works. If the lowerLevelPart is further down the bom it fails.
If I use X123546 it returns X323213H6XW001
If I use CH6780 It fails to return any thing. It should return X323213H6XW001
0
 
kemi67Commented:
I've tested your original query on your sample data, and it does not return all the components, but only this
X323213H6XW001      NULL      CH6780
X323213H6XW001      NULL      D45367
X323213H6XW001      NULL      PD34525
X323213H6XW001      NULL      X568974

You see that X123546 is missing.
You should have some other data in Inventory that interfers with the query; I've put in inventory table only the X323213H6XW001code

My query is based on yours, and it works for all inputs but X123546

So I've tested this new one that works with your data:


 
SELECT     distinct X.StockCode AS topLevelPart 
                      
FROM         (SELECT     InvMaster.StockCode AS StockCode, InvMaster.PartCategory AS PartCategory
                       FROM          InvMaster
                       GROUP BY InvMaster.StockCode, InvMaster.PartCategory) X INNER JOIN
                      dbo.BomStructure A ON A.ParentPart = X.StockCode LEFT OUTER JOIN
                      dbo.BomStructure B ON B.ParentPart = A.Component LEFT OUTER JOIN
                      dbo.BomStructure C ON C.ParentPart = B.Component LEFT OUTER JOIN
                      dbo.BomStructure D ON D.ParentPart = C.Component LEFT OUTER JOIN
                      dbo.BomStructure E ON E.ParentPart = D.Component


where 'CH6780' IN (E.Component, D.Component, C.Component, B.Component, A.Component )

Open in new window


I've made the test filling BromTable with your data and inserting only  X323213H6XW001in inventory
0
 
RickCooperAuthor Commented:
kemi67,

 The code works for one component is it possible to have a list of lower level components?

ie. where ('CH6780','XYZ','RET3') IN

I have over 100 lower level parts I need to get the top level for.

If it's not possible I will accept your solution as it answers the question I originally posted.

0
 
kemi67Commented:
How is the way that you pass the component list?
The solution will be simple if you can fill a temporary table with component parts.
Otherways it seems that a stored procedure can do the job, passing a formatted string

Supposing that you fill a temporary CompTable:
CompTable.ArtCode
CH6780
XYZ
RET3

You can use the following
SELECT DISTINCT X.StockCode AS topLevelPart, CompTable.ArtCode
FROM         (SELECT     StockCode, PartCategory
                       FROM          InvMaster
                       GROUP BY StockCode, PartCategory) AS X INNER JOIN
                      BomStructure AS A ON A.ParentPart = X.StockCode LEFT OUTER JOIN
                      BomStructure AS B ON B.ParentPart = A.Component LEFT OUTER JOIN
                      BomStructure AS C ON C.ParentPart = B.Component LEFT OUTER JOIN
                      BomStructure AS D ON D.ParentPart = C.Component LEFT OUTER JOIN
                      BomStructure AS E ON E.ParentPart = D.Component CROSS JOIN
                      CompTable
WHERE     (CompTable.ArtCode IN (E.Component, D.Component, C.Component, B.Component, A.Component))

Open in new window

0
 
RickCooperAuthor Commented:
I can create the temporary table for the componants.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now