The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

I have a bill of material list from a table, which I will try to convert over to a different BOM table. The list I have is based on a column with materials and levels. But I need to create a list of materials and components. I have illustrated below how the list looks and how I want to convert it. There can be up to 10 levels. But this is too tricky for me. Someone who can help me?

Exampel how it looks like today:

ID      Material        SLEVEL      QTY
-----------------------------------------------------------
1       Material123     0           1
2       9515463A        1           1
3       1734279         2           1
4       3201463         2           4
5       9515463B        1           1
6       1734163         2           1
7       3201463         2           4
9       8349834         2           2
10      1349833         3           2
11      1463918         3           4
12      Material456     0           1
13      9512070         1           1
14      1621405         2           1
15      1734534         2           2
16      1734535         2           1
17      9513294         1           1
18      1621750         2           3

I want to make a SQL that change the BOM list so it becomes like this:

Material        Component   Qty SLevel
-------------------------------------------------
Material123     9515463A    1   0
Material123     1734279     1   0
Material123     3201463     1   0
Material123     9515463B    1   0
Material123     1734163     1   0
Material123     3201463     1   0
Material123     8349834     1   0
Material123     1349833     1   0
Material123     1463918     1   0
9515463A        1734279     1   1
9515463A        3201463     4   1
9515463B        1734163     1   1
9515463B        3201463     4   1
9515463B        8349834     2   1
8349834         1349833     2   2
8349834         1463918     4   2
Material456     9512070     1   0
Material456     1621405     1   0
Material456     1734534     1   0
Material456     1734535     1   0
Material456     9513294     1   0
Material456     1621750     1   0
9512070         1621405     1   1
9512070         1734534     2   1
9512070         1734535     1   1
9513294         1621750     3   1

asked 16 Oct '15, 14:53

Rolle's gravatar image

Rolle
379243342
accept rate: 0%

edited 16 Oct '15, 14:54

Do you have had a look at other related FAQs here (hint: search for "recursion") or the SA docs, say

Parts explosion problem?

(16 Oct '15, 15:29) Volker Barth

I have checked this but unfortunately it becomes too complex for me to get to where this is not the same principle. I know it goes, but the question is how. Do you have any idea?

(16 Oct '15, 16:02) Rolle
Replies hidden
1

Well, to me it's not clear how the different parts/components are connected. Does the order of the entries matter here? (I usually would expect a parent/child relationship here for each entry.)

FWIW, if you have a question how to build an according SQL query, I strongly recommend that you try to tell about the business logic as much as possible. Just to show an input sample and a desired result is surely helpful but not sufficient to make it easy for others to help. And it would be really helpful if you would supply a sample script to build the original table and its data.

(17 Oct '15, 16:15) Volker Barth

The main material always starts when Slevel is 0. The main material component are all subsequent slevel 1. Slevels 1 components are all subsequent slevel 2 and so on. This will run until slevel is 0 again and this will start again as above. There can be up to 10 levels per main material. ID field is just a counter. I hope I have clarified it a bit ...

(17 Oct '15, 16:38) Rolle
Replies hidden

It's still not clear to me: How does material 9515463A "know" that it belongs to main material Material123 and not to Material456 as both have Slevel = 0?

In my understanding the ID order seems relevant here but that would not look like a relational-based approach (i.e. there's no FK link between main parts and components) and you also tell that "ID field is just a counter".

Please note that rows in a table in a RDMS are unordered by design.

As stated, it would be helpful if you could supply a small script to create and fill the table with your sample data.

(20 Oct '15, 02:28) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×17

question asked: 16 Oct '15, 14:53

question was seen: 1,100 times

last updated: 20 Oct '15, 02:28