Mike, Just wanted to thank you for the detailed information. This has helped me. I was thinking along these lines but was not able to figure it out for one table. Also the code to create a table showing everything (illustrating the DOS EXPLODE command) helped. I had not use the UNION command much.
I still have to figure the report and care for a couple of mistakes in my code. Have a Happy Thanksgiving. James Belisle Making Information Systems People Friendly Since 1990 -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of MikeB Sent: Wednesday, November 25, 2015 9:46 PM To: Jim Belisle Subject: [RBASE-L] - Re: BOM Well I dug this out of a dusty file folder on the server: """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" EXPLODING BILL OF MATERIALS REPORTS """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" PRODUCT : R:BASE VERSION : 3.1 CATEGORY : PROGRAMMING SUBCATEGORY : TOOLS """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" Bill of Materials (BOM) applications have something in common with agricultural and biological applications that track animal blood lines. Both require a method for managing what's called the exploding parts (population) problem. Products are made up of components that are in turn made up of other parts, materials, or components. An animal produces offspring that in turn produce their own offspring. Both situations can create explosion diagrams similar to this one: The January 1989 "R:BASE EXCHANGE" showed you how to use a database design and an R:BASE for DOS program--using SET POINTER in a WHILE loop--to explode the parts in a bill of materials report. Now, this article shows you how to do the same thing using SQL and the same database design. You'll see a single complex SELECT command do in R:BASE 3.1 what it took an entire program to do in R:BASE for DOS. Diagram of an Explosion The diagram below shows a parts explosion. This example has four levels. The product (PROD1) explodes into its components, and each component explodes into the materials that comprise it, and one of the materials (M1) is made up of submaterials. Level One: Product | Level Two: Component | | Level Three: Material | | | Level Four: Submaterial | | | | | | | | | | | | | | | +---- Sub1 | | +----- M1 --| | | | +---- Sub2 | +------ A --|----- M2 | | |----- M3 | | +----- M4 | | | | +---- Sub1 Prod1 -----| +----- M1 --| | | +---- Sub2 |------ B --|----- M2 | +----- M3 | | +------ C -------- M5 The Database Design """"""""""""""""""" To produce the explosion diagram from the data, you need a database design that can accommodate any number of levels. The database needs to be flexible enough to adapt to many different kinds of explosions that use different kinds of components, materials, and submaterials. Microrim recommends that you use the concept of parent-child relationships to build the database. For example, look at the explosion diagram shown above. Think of each element in the diagram as a parent of zero, one, or more other elements (children) and as a child of one or more parents. Design and Create the BOM To represent these relationships in your database, create a table (BOM) with two columns: PARENT (TEXT 10), and CHILD (TEXT 10). CREATE TABLE bom (parent TEXT 10 + NOT NULL, child TEXT 10 NOT NULL) This database design works with an unlimited number of levels, and because all the BOM relationships are in one table, it's easier to track a path through the explosion--relationship by relationship. Each path through the diagram is unique. There's only one PROD1-B-M3 path and only one PROD1-B-M1-SUB2 path. Load the BOM """""""""""" Load BOM with a row for every relationship. For example, enter the following lines at the R> prompt to load rows for the four-level BOM diagram shown above. LOAD bom USING parent, child PROD1 A PROD1 B PROD1 C A M1 A M2 A M3 A M4 B M1 B M2 B M3 C M5 M1 SUB1 M1 SUB2 M2 'The End' M3 'The End' M4 'The End' M5 'The End' SUB1 'The End' SUB2 'The End' END You need a row for every possible path, including the parents that have no children. The SQL Explosion """"""""""""""""" Now with the database built and loaded, you can use the SQL SELECT command (EXPLODE.PAR or EXPLODE.KID) to weave a path through the explosion. That is, you can specify a parent and see all the children, or specify a child and see all the parents. You can use the SELECT to display the explosion, or you can use it to specify the rows for a DECLARE CURSOR path, to create a view, or with the INSERT command to specify the rows that you want to add to another table. See a Parent's Children """"""""""""""""""""""" To specify a parent part and see all the children parts, run EXPLODE.PAR: *( EXPLODE.PAR--given a parent, find the children.) DIALOG 'Enter the name of the parent part: ' vparent vkey 1 SELECT t1.parent, t1.child, t2.child, t3.child, t4.child + FROM bom t1, bom t2, bom t3, bom t4 + WHERE t1.parent=.vparent AND t1.child=t2.parent + AND t2.child=t3.parent AND t3.child=t4.parent + UNION SELECT t1.parent, t1.child, t2.child, t3.child, ' ' + FROM bom t1, bom t2, bom t3 + WHERE t1.parent=.vparent AND t1.child=t2.parent + AND t2.child=t3.parent AND (t3.child NOT IN + (SELECT t4.parent FROM bom t4)) + UNION SELECT t1.parent, t1.child, t2.child, ' ', ' ' + FROM bom t1, bom t2 + WHERE t1.parent=.vparent AND t1.child=t2.parent + AND (t2.child NOT IN (SELECT t3.parent FROM bom t3)) + UNION SELECT t1.parent, t1.child, ' ', ' ', ' ' + FROM bom t1 + WHERE t1.parent=.vparent + AND (t1.child NOT IN (SELECT t2.parent FROM bom t2)) See a Child's Parents """"""""""""""""""""" To specify a child part and see all the parent parts, change all the CHILD column references to PARENT and all the PARENT column references to CHILD, as in EXPLODE.KID. *( EXPLODE.KID--given a child, find the parents.) DIALOG 'Enter the name of the child part: ' vchild vkey 1 SELECT t1.child, t1.parent, t2.parent, t3.parent, t4.parent + FROM bom t1, bom t2, bom t3, bom t4 + WHERE t1.child=.vchild AND t1.parent=t2.child + AND t2.parent=t3.child AND t3.parent=t4.child + UNION SELECT t1.child, t1.parent, t2.parent, t3.parent, ' ' + FROM bom t1, bom t2, bom t3 + WHERE t1.child=.vchild AND t1.parent=t2.child + AND t2.parent=t3.child AND (t3.parent NOT IN + (SELECT t4.child FROM bom t4)) + UNION SELECT t1.child, t1.parent, t2.parent, ' ', ' ' + FROM bom t1, bom t2 + WHERE t1.child=.vchild AND t1.parent=t2.child + AND (t2.parent NOT IN (SELECT t3.child FROM bom t3)) + UNION SELECT t1.child, t1.parent, ' ', ' ', ' ' + FROM bom t1 + WHERE t1.child=.vchild + AND (t1.parent NOT IN (SELECT t2.child FROM bom t2)) How Does EXPLODE.PAR Work? """""""""""""""""""""""""" You don't have to understand how EXPLODE.PAR works in order to use it, but you do need to understand it if you plan on modifying it. By using correlation names (T1, T2, T3, and T4), you can select data from the same table four times--just as if the four were different tables. Essentially, you're joining the BOM table with itself to make as many copies as you have levels of children. In this example, there are four levels of parents, so four copies (T1, T2, T3, and T4) of the BOM table are used in the SELECT command. There are four SELECT commands in one. The First SELECT """""""""""""""" The first SELECT in EXPLODE.PAR finds all the rows that go all the way through all four levels. The WHERE clause steps through the explosion like this: o Find all the rows in T1 that match the parent part requested. o Take the child parts from those T1 rows and find all rows in T2 where those child parts are parent parts. o Take the child parts from those T2 rows and find all the rows in T3 where those child parts are parent parts. o Take the child parts from those T3 rows and find all the rows in T4 where those child parts are parts in level four. If you had more levels, you'd add more to the WHERE clause until you reach the final level. This first SELECT finds the rows that have parts that are represented in all four levels, and it finds only these rows. If a part is a child in T2 but not a parent in T3 the row will not be found in this first SELECT. It will, however, be found later by one of the three UNION SELECTs. These other three SELECT clauses find the rows that stop after three, two, and one levels. The Second SELECT """"""""""""""""" Next, the UNION operator joins the rows found by the first SELECT with the rows found by the second SELECT. This second SELECT finds rows that go through the first three levels only. Since you won't be finding a part on level four, you need to place a constant (a blank enclosed in single quotation marks) in the SELECT list and only select rows from three copies of the BOM table. The final condition on the WHERE clause for this second SELECT is a sub-SELECT that finds the rows that are children in level three but not a parent in level four. The Third & Fourth SELECTs """""""""""""""""""""""""" The third and fourth SELECTs finish the job. The third SELECT gets the child parts that are not parents in level three, and the fourth gets the parts that are not parents in level two--that is, these parts exist only in the first level (T1). Each time the new set of rows that are selected by the SELECT clause are appended to the previously selected rows by the UNION operator. If you had fewer levels, you'd need fewer UNION SELECT groups, or missing levels would be left blank. If you had more levels, you'd need more UNION SELECT groups. The SELECT command assumes that for every parent part entered there is an entry in the child column. In other words, it assumes that there are no null values in either column. Outer and Inner Joins Are Used """""""""""""""""""""""""""""" This technique uses both outer joins (the UNION operator joins together SELECT clauses) and inner joins (each SELECT command selects rows using more than one copy of the BOM table). By studying this example, you can become more proficient at using complex SELECT commands, but remember that the more inner and outer joins you do, the more time it will take the computer to do the job. In fact, joins this complex can take days to complete depending on how big the BOM table is. This is only a beginning. Here you learned how to design a database and write a complex SELECT statement to explode a product into its component parts and materials. But remember that the exploding parts problem is complex. This database design and UNION SELECT solution won't solve every exploding parts problem. It's a beginning. > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf Of Albert > Sent: Wednesday, November 25, 2015 11:13 AM > To: [email protected] > Subject: [RBASE-L] - Re: BOM > > I would use a temporary reporting table for this. The columns to > report would be as follows or both Assy/Sub part numbers and > descriptions. I find that system easier than using report variables. > Assembly > Assembly description > Sub Level 1 > Description Level 1 > Sub Level 2 > Description Level 2 > Sub Level 3 > Description Level 3 > > Load the table with a stored procedure, repeating the values in each > column and adding the subs as needed. > > Each column would be reported with repeated values hidden. That should > give you what you need. > > Albert > > On 2015-11-25 8:57 AM, Jim Belisle wrote: > > For years we have costed our products with a simple report just > throwing together all parts making up a product not worrying about > using an indented BOM. > > We would like to go to an indented BOM. > > Right now it is just > > Model# then all parts making up the model. > > > > We want something like this: > > Level 1: Model# > > Level 2: Assemblies and parts we ship > > Level 3: Sub assemblies (or parts if no subs) > making up the assemblies. > > Level 4: Parts making up the sub > assemblies. > > > > I can get it so all assemblies are together and then all subs > together by assigning a BOMtype to them. > > So it will be: > > Assy > > Assy > > Assy > > Sub assy > > parts > > Sub assy > > parts > > Sub assy > > Parts > > > > That is not however what we want. I just can't seem to figure the > proper way to handle this. > > Any blues clues would be appreciated. > > > > James Belisle > > > > Making Information Systems People Friendly Since 1990 > > [cid:[email protected]] > > > >
