Karen: My learning curve question:
Isn't this really a normalization problem, where the BOM is in fact logically a VIEW composed of parts, sub-assemblies of nested parts and assemblies of sub-assemblies; all being treated as 'parts'? Bruce > -------- Original Message -------- > Subject: [RBASE-L] - Re: Exploding BOM > From: [email protected] > Date: Thu, March 15, 2012 1:16 pm > To: [email protected] (RBASE-L Mailing List) > > > Tony: Took me a while to figure out what your code does, but now I > understand it. Quite clever!! > I'm not sure I can assign a "level" to each stage of my BOM, though, > without someone having to do alot of research into the parts. > > Right now, they know that Part 100 is composed of parts 200 and 201 and > that's all they would have to type in. They do not have to type in that part > 200 is composed of 300 and 301 as part of the hierarchy of part 100, and I > wouldn't want them to have to do that. Somewhere along the line they had > already typed in that part 200 is composed of 300 and 301, but I wouldn't > want > that redundency repeated as part of the data for part 100. I'm hoping that > made sense. Part 200 is a level one, with 300 and 301 being level two of > that part. But part 200 would also be level two of part 100. > > Discussing the opinions so far (and sharing those interesting links from > Ben) with the consultant and the client, the client said "screw it, just > figure on 3 levels and be done with it". Easy for him to say now because he > has > only one level at this point and doesn't actually foresee it going into > "components of components" into the future. So although I will read > further > responses, and implement new code if it's easy enough to do, I got > permission to do 3 nested cursors and I'm cool with that! > > Karen > > > In a message dated 3/15/2012 2:00:11 PM Central Daylight Time, > [email protected] writes: > > Suppose you have the table (In my example I am talking about a Project > > table, which contains subprojects, sub-subprojects and so on) > > > > > > > > PJT_Project > > > > > > > > Columns: > > > > > > > > PJT_ID Primary key > > PJT_ID_refer_to Refers to superior project (foreign key) > > PJT_Level Level of the project > > > > > > > > The level is essential, because then you are able to select all rows of > > the same level in the BOM > > The only thing you need to do is assign the right level to the row, when > > it is added to the BOM > > > > The code then looks like below, where T_PJT_PROJECT is a temp table, where > > PJT_Level is a column in this temp table : > > > > > > APPEND PJT_PROJECT TO T_PJT_PROJECT+ > > WHERE PJT_ID = .V_PJT_ID > > > > SET VAR VSP_PJT_level INTEGER = (.V_PJT_level) > > > > n To enforce the loop is performed at least once > > > > SET VAR VSP_PJT_ID_ATL INTEGER = 100 > > > > WHILE VSP_PJT_ID_ATL >0 THEN > > > > -- loop to append the the rows > > > > SET ERROR MESSAGE 705 OFF > > > > DROP CURSOR C10 > > > > SET ERROR MESSAGE 705 ON > > > > DECLARE C10 CURSOR FOR SELECT + > > > > PJT_ID + > > > > FROM T_PJT_PROJECT + > > > > WHERE PJT_level = .VSP_PJT_level > > > > OPEN C10 > > > > FETCH C10 INTO VSP_PJT_ID INDICATOR I1 > > > > WHILE SQLCODE <>100 THEN > > > > APPEND PJT_PROJECT TO T T_PJT_PROJECT + > > > > WHERE PJT_ID_REFER_TO = .VSP_PJT_ID > > > > FETCH C10 INTO VSP_PJT_ID INDICATOR I1 > > > > ENDWHILE > > > > -- cursor c10 > > > > DROP CURSOR C10 > > > > SET VAR VSP_PJT_ID_ATL INTEGER = 0 > > > > SET VAR VSP_PJT_level = (.VSP_PJT_level + 1) > > > > SELECT COUNT(PJT_ID) INTO VSP_PJT_ID_ATL INDICATOR I1 + > > > > FROM T_PJT_PROJECT + > > > > WHERE PJT_level = .VSP_PJT_level > > > > ENDWHILE > > > > > > > > In this way the number of levels is unlimited, the created temp table can > > be used to do calculate the number of parts needed and administer it in > > your system > > > >
