Karen,
This has been an interesting discussion and I would like to see how you set up the table to do your cursors. You may come up with a simpler way of doing what we do now. We have an inventory system in which we have three types of parts: 1) Stand alone parts 2) Parts that are part of an assembly 3) Assemblies Your cursor may be a simpler way of us caring for our inventory. I now use a series of temp tables and views to calculate our needs based on the open orders. We also use an allocation system so we can allocate specific parts to our orders ahead of time. James Belisle Making Information Systems People Friendly Since 1990 ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Thursday, March 15, 2012 3:17 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Exploding BOM 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
