Solved

Need to Add Levels for Indented BOM Explosion Dataset Using SQL

Posted on 2009-07-08
15
1,380 Views
Last Modified: 2012-05-07
Hello Experts!

As you can see from the attached file containing example data and the desired output, not only are there many levels of parent/child records, but you also have to make sure you're accessing the latest revision of the part number where the effectivedate <= today's date.

A partial BOM explosion would go like this (Partnum, Revisionnum):

TOP LEVEL 0:  NC006000    C
      LEVEL 1:  NC00M021    6  (NO FURTHER BOM EXPLOSION ON THIS PART NUMBER)
        LEVEL 1:  NC00M022    5  (NO FURTHER BOM EXPLOSION ON THIS PART NUMBER)
      LEVEL 1:  NC006303    B
            LEVEL 2:  NC006301   B
            LEVEL 2:  NC006140   K
                  LEVEL 3:  NC006141   K
                  ...and so on.

I only need to perform this explosion for one part number at a time, in this case NC006000.  This will always be a hardcoded part number because this query is not to be run by anybody but a programmer at this point.

I have very little SQL experience but am a good fast learner.  I've been scouring many different books, forums, web sites, and any other source I can get my paws on for an answer for this.  I've seen some good answers on EE but they usually are based on tables that (conveniently!) include the BOM levels.  I somehow have to count them as I go and that is just over my head!  

I found a snippet of recursive code that I've been playing with but have no idea how to add levels.  Help???


WITH RPL AS (SELECT     PARTMTL.PARTNUM, PARTMTL.MTLPARTNUM, PARTMTL.QTYPER
                                FROM         dbo.partmtl
                                WHERE     (PARTMTL.PARTNUM = 'NC006000')
                                UNION ALL
                                SELECT     CHILD.partnum, CHILD.mtlpartnum, CHILD.qtyper
                                FROM         RPL AS PARENT INNER JOIN
                                                      dbo.partmtl AS CHILD ON PARENT.MTLPARTNUM = CHILD.partnum)
    SELECT DISTINCT PARTNUM, MTLPARTNUM, QTYPER
     FROM         RPL AS RPL_1
     ORDER BY PARTNUM, MTLPARTNUM, QTYPER

Open in new window

BOM-Data-Output.txt
0
Comment
Question by:MTaft
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
15 Comments
 
LVL 1

Author Comment

by:MTaft
ID: 24808185
The deepest level would be 8.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24811354
Sounds like a CTE query to me... Will have a look at your txt file and get back to you...

Any chance of getting the three tables as spreadsheets ?
0
 
LVL 1

Author Comment

by:MTaft
ID: 24813261
Sure, I've just stripped out the fields you need for this query.  Each table is on a different Excel tab.  They are all linked by company ("NC") and partnum.
Sample-Data-for-BOM.xlsx
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

Expert Comment

by:Mark Wills
ID: 24813352
Thanks for that, was getting very caught up on mtlpartnum existing in parts table and then the revnum not matching...
0
 
LVL 1

Author Comment

by:MTaft
ID: 24813395
I can't tell you how much I appreciate your help.  I've been struggling for days on this, and I am an IT department of one at the little company I work for.  Although I've been programming for 10+ years in Progress, I've never had to do much more than a basic select statement in SQL.  Do you have any recommendations for books or other resources that go into good concise detail on the more complex SQL logic that's possible?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24814231
Have played with Progress. Bit different :)  As to best books, there are a few, suggest looking at MS Press as a starting point, and then there is always EE :)

Right...

First challenge was to get the current versions. Easiest way was to create a view on the PARTREV table so that every PARTNUM has only 1 REVISIONNUM.

A VIEW is like a "virtual" table. It is created just the once, and can be accessed much like any table there after.

The second step was to get the BOM exploding the correct way. We use a CTE query to do this - basically a (recursive) query that allows you to select back on itself.

Have a look below...

-- step 1 and a once off task, once run, then do not have to worry about creating the view again.
-- a view is like a virtual table - it is resolved at runtime. This one is giving us the current revisionnum as at "today"
 
create view vw_PARTREV as
Select P.PARTNUM, P.REVISIONNUM, P.EFFECTIVEDATE
from PARTREV P
inner join (select partnum, max(effectivedate) as effectivedate from partrev where effectivedate <= getdate() group by partnum) B on B.partnum = P.partnum and B.effectivedate = P.effectivedate
GO
 
-- just to check, have a look
 
select * from vw_PARTREV
 
-- now the real query...
-- a recursive query - must be the first command in a batch - easiest way is to prefix it with a semicolon
-- basic syntax is :  with cte as (select stuff) select * from cte 
 
;with CTE_BOM as
(
     select 0 as level, 000 as seq, part.partnum as parent, part.partnum, revisionnum , description, convert(decimal(18,8),0) as qty, ium, convert(varchar(800),'0') as path  from part inner join vw_partrev on part.partnum=vw_partrev.partnum
     where part.partnum = 'NC006000'
 
     union all
 
     select B.level+1, convert(int,M.mtlseq), M.partnum, M.mtlpartnum, R.revisionnum, P.description, convert(decimal(18,8),M.qtyper), P.IUM, convert(varchar(800),b.path+','+convert(varchar(10),M.mtlseq))
     from CTE_BOM B
     inner join PART P on P.partnum = B.partnum
     inner join vw_PARTREV R on R.partnum = B.partnum 
     inner join PARTMTL M on M.partnum = R.partnum and M.revisionnum = R.revisionnum
)
select *,replicate('....',level)+partnum as tree from CTE_BOM
Order by level,parent,seq,partnum     -- or order by path to get indented BOM

Open in new window

0
 
LVL 1

Author Comment

by:MTaft
ID: 24814319
What a wonderful explanation.  Thank you!

Now, when I try to run it as a query, it comes up with this error:

'CREATE VIEW' must be the first statement in a query batch.

And except for the comments, it is, so that doesn't make any sense to me.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24814420
open a new QUERY window in SSMS (shortcut key = ctrl+N), copy just the create view lines 4 through to 7 and press execute (shortcut key = F5)

You can also highlight just the required code and press F5 to execute just the highlighted (selected) code.
0
 
LVL 1

Author Comment

by:MTaft
ID: 24814749
I see.. so when you say it's a once-only deal, it actually created something permanent in the db.  

The only two things that is not quite right with this breathtakingly lovely piece of code (and it is entirely my fault for not specifying) is that the revisionnum must be the revision num of the child part number, and the description must be the description of the child part number.  I'm going to fiddle with it to see if I can get it because darn it, I should be able to figure SOMETHING out on my own!
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24814897
Ah yes, picking it up in the wrong sequence...


;with CTE_BOM as
(
     select 0 as level, 000 as seq, part.partnum as parent, part.partnum, revisionnum , description, convert(decimal(18,8),0) as qty, ium, convert(varchar(800),'0') as path  from part inner join vw_partrev on part.partnum=vw_partrev.partnum
     where part.partnum = 'NC006000'

     union all

     select B.level+1, convert(int,M.mtlseq), M.partnum, M.mtlpartnum, R.revisionnum, P.description, convert(decimal(18,8),M.qtyper), P.IUM, convert(varchar(800),b.path+','+convert(varchar(10),M.mtlseq))
     from CTE_BOM B
     inner join PARTMTL M on M.partnum = B.partnum
     inner join vw_PARTREV R on R.partnum = B.partnum and R.revisionnum = M.revisionnum  
     inner join PART P on P.partnum = M.mtlpartnum
)
select *,replicate('....',level)+partnum as tree from CTE_BOM
Order by level,parent,seq,partnum     -- or order by path to get indented BOM

0
 
LVL 1

Author Comment

by:MTaft
ID: 24814931
Almost.... that fixed the description but not the revision level.  Still showing the revision level of the parent part number.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 24815416
OK. We might have a small challenge...

We must have the revision number for the Parent to get the correct entry from PARTMTL...

So, maybe we can pick up both, and then you can use the most appropriate :


;with BOM as
(
     select 0 as level, 000 as seq, part.partnum as parent, revisionnum as P_revisionnum, part.partnum, revisionnum as C_revisionnum , description, convert(decimal(18,8),0) as qty, ium, convert(varchar(800),'0') as path  from part inner join vw_partrev on part.partnum=vw_partrev.partnum
     where part.partnum = 'NC006000'

     union all

     select B.level+1, convert(int,M.mtlseq), M.partnum,  R.revisionnum, M.mtlpartnum, CR.revisionnum, P.description, convert(decimal(18,8),M.qtyper), P.IUM, convert(varchar(800),b.path+','+convert(varchar(10),M.mtlseq))
     from BOM B
     inner join PARTMTL M on M.partnum = B.partnum
     inner join vw_PARTREV R on R.partnum = B.partnum and M.revisionnum = R.revisionnum
     inner join PART P on P.partnum = M.mtlpartnum
     inner join vw_PARTREV CR on CR.partnum = P.partnum  
)
select *,replicate('....',level)+partnum as tree from BOM
Order by level,parent,seq,partnum     -- or order by path to get indented BOM



0
 
LVL 1

Author Closing Comment

by:MTaft
ID: 31601239
Thank you so much!  This is EXACTLY what I needed, and you explained it very well.  I will definitely be able to re-use and modify this logic and know what it means.  Thank you again!
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24816148
A pleasure working with you and very happy to have been of assistance...
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

724 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