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 > >
