Solved

SQL Query Problem

Posted on 2011-02-23
14
301 Views
Last Modified: 2012-05-11
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
Comment
Question by:RickCooper
  • 7
  • 6
14 Comments
 
LVL 8

Expert Comment

by:vinurajr
ID: 34959543
which table contains the component part..? and what do you mean by code only return the toplevelpart..?

can you give some sample..
0
 
LVL 1

Author Comment

by:RickCooper
ID: 34959624
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
 
LVL 7

Expert Comment

by:kemi67
ID: 34959636
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
 
LVL 1

Author Comment

by:RickCooper
ID: 34959675
Kemi67,

 I have tried you code and it returns no top level part when I enter a lowerlevel
0
 
LVL 7

Expert Comment

by:kemi67
ID: 34959692
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
 
LVL 1

Author Comment

by:RickCooper
ID: 34959709
Hi,

 I changed the LowerPart to lowerLevelPart when I originally tested and it did not return any parts.
0
 
LVL 7

Expert Comment

by:kemi67
ID: 34959720
Following your example, which lower code did you use?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 1

Author Comment

by:RickCooper
ID: 34959725
X568974
0
 
LVL 7

Expert Comment

by:kemi67
ID: 34959780
Did you have no result or error?
Can you run the query without the WHERE clause and tell what happens?
0
 
LVL 1

Author Comment

by:RickCooper
ID: 34959814
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
 
LVL 7

Expert Comment

by:kemi67
ID: 34961784
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
 
LVL 1

Author Comment

by:RickCooper
ID: 34961966
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
 
LVL 7

Accepted Solution

by:
kemi67 earned 500 total points
ID: 34962106
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
 
LVL 1

Author Closing Comment

by:RickCooper
ID: 34962236
I can create the temporary table for the componants.

Thanks
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now