Karen,
I have two solutions for this problem.
Essential in the solution are:
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
I have also another solution available, which is not based upon decelare cursor.
Hope this helps
Tony IJntema
From: [email protected] [mailto:[email protected]] On Behalf Of
[email protected]
Sent: donderdag 15 maart 2012 16:55
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: Exploding BOM
Dan: That's the way I was going to go, a definite number of cursors. I feel
better knowing that at least one person did it in that manner! I'll give it a
while
and if no one has code for unlimited levels, then I'll just go with that.
Thanks!
Karen
In a message dated 3/15/2012 10:44:55 AM Central Daylight Time,
<mailto:[email protected]> [email protected] writes:
I use cursors. I just set up for expected level of boms that we could possibly
imagine using. We only go to a max of 6 levels down into the bom so I put in 8
cursor levels to just in case and put an error message if it finds something
below 8 levels so I can add more someday.
I know Rbase does not support celko’s nested boms so I use cursors instead.
Dan
