Solved

SQL Query Problem

Posted on 2011-02-23
14
300 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

758 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

20 Experts available now in Live!

Get 1:1 Help Now