Programming challenge with BOMs (2)

Somehow I doubt that anyone is going to take up my challenge, so I'll try and document my code here.  First, I'll go through some descriptions of the challenge: every line/part in the BOM has a level; direct sons of the father part have level 1, sons of the sons have level 2, etc. If the BOM consisted solely of direct sons, then their numbers would be 1, 2, 3, etc. When a grandson is encountered, the current level 1 number has to be remembered whilst the grandsons get numbered consecutively (i.e. 3.1, 3.2, 3.3). When there are no more grandsons then the program has to continue incrementing the previous level number.

In simple programming terms, a 'previous level' variable is maintained; this variable is compared to the 'current level' variable. If the two levels are the same then the 'level counter' is incremented; if the current level is greater than the previous level then a new counter should commence, and should the current level be less than the previous level, then the previous counter should be restored.

What took me some time to figure out was how I maintain these different level counters. If I were using a normal imperative programming language then I would use an array to do this, but the Priority flavour of SQL doesn't have arrays. Another possibility would be pushing the current level counter onto a stack, then later popping that value; this could be done by means of recursion or a software stack implementation (which can be emulated by an array), but again, these are tools that I don't have at my disposal.

I'm not sure whether the hint came from that phrase, pushing onto a stack, or maybe it was my right brain hemisphere delivering the goods, but suddenly it became clear that I could use a database table (specifically, STACK2) to implement the array. STACK2 has two fields: ELEMENT (the key) and TYPE, so ELEMENT will hold the level and TYPE the counter for that level. Once I had this key element (sorry!) sorted, the rest of the algorithm fell into place.

While I was still going over the algorithm in my head, I considered how many rows I would need in STACK2: what would happen if I defined a given number of rows then the BOM had one more level? Then I realised that I could first establish the maximum level in the BOM then enter rows appropriately. Entering the rows at the beginning means that from hereon the procedure only has to UPDATE STACK2 - there will be no need to test whether the required row exists thus no need for INSERTs.

LINK STACK2 TO :$.ST2;
ERRMSG 1 WHERE :RETVAL <= 0;
:MAX = 0;
SELECT MAX (VAR) INTO :MAX
FROM PARTARC;
:PREVIOUS = 0;
LABEL 1;
:PREVIOUS = :PREVIOUS + 1;
INSERT INTO STACK2 (ELEMENT) VALUES (:PREVIOUS);
LOOP 1 WHERE :PREVIOUS < :MAX;
:PREVIOUS = 0;
DECLARE C30A CURSOR FOR
SELECT SONACT, VAR
FROM PARTARC
WHERE SONACT > 0
ORDER BY SONACT;
OPEN C30A;
GOTO 300 WHERE :RETVAL <= 0;  /* ??? */
LABEL 100;
FETCH C30A INTO :SONACT, :LEVEL;
GOTO 200 WHERE :RETVAL <= 0;
GOTO 110 WHERE :PREVIOUS < :LEVEL;
GOTO 120 WHERE :PREVIOUS = :LEVEL;
GOTO 130 WHERE :PREVIOUS > :LEVEL;
LABEL 110;
:PREVIOUS = :LEVEL;
UPDATE STACK2
SET TYPE = 1
WHERE ELEMENT = :PREVIOUS;
GOTO 150;
LABEL 120;
UPDATE STACK2
SET TYPE = TYPE + 1
WHERE ELEMENT = :PREVIOUS;
GOTO 150;
LABEL 130;
:PREVIOUS = :LEVEL;
UPDATE STACK2
SET TYPE = TYPE + 1
WHERE ELEMENT = :PREVIOUS;
LABEL 150;
:STRING = :STR = '';
SELECT ITOA (TYPE) INTO :STRING
FROM STACK2
WHERE ELEMENT = 1;
GOTO 160 WHERE :PREVIOUS = 1;
:N = 1;
LABEL 1;
:N = :N + 1;
SELECT ITOA (TYPE) INTO :STR
FROM STACK2
WHERE ELEMENT = :N;
:STRING = STRCAT (:STRING, '.', :STR);
LOOP 1 WHERE :N < :PREVIOUS;
LABEL 160;
UPDATE STACK4
SET DOCNO = :STRING
WHERE KEY = :SONACT;
LOOP 100;
LABEL 200;
CLOSE C30A;
LABEL 300;
UNLINK AND REMOVE STACK2;
 

The first part of the code (upto 'DECLARE C30A CURSOR') is concerned with setting up STACK2 as I described previously. The cursor then iterates over the exploded BOMs, retrieving the key number (saved in SONACT) and the line level (saved in VAR). As I noted in an earlier blog on the topic, these fields have one set of meanings when PARTARC is 'raw' and another set of meanings when PARTARC holds exploded BOMs, after having run SONRAW. This is somewhat distracting.

The next part of the code - the three comparisons between the previous level and the current level - implements what I described in the second paragraph of this blog. Following this, the code beginning at label 150 is concerned with building the string to be saved; level 1 is made into a special case - this is in order to handle the separating dots more easily. It doesn't matter that there might be values in the later rows of the table - the loop (that ends with LOOP 1 WHERE :N < :PREVIOUS) means that these values won't be accessed. The final statement saves the calculated string in the field STACK4.DOCNO.

I am proud to say that I worked all of this out in my head before I typed it last night, although there was one small glitch that caused the procedure not to work. By mistake, I had defined that the insertions into STACK2 at the beginning were into field TYPE, which is completely wrong. ELEMENT is the key of this table and as no value was inserted, nothing went into STACK2, making the rest of the program fairly useless. Once I corrected this, the procedure worked exactly as it was supposed to.

I really would be interested to see how other people would solve this challenge, as I make no claims that the above code is optimal.

Programming challenge with Bills Of Materials

The standard reports for displaying bills of materials (BOMs) that come with Priority show the depth of a part in the BOM in a field that is slightly complicated to program. Instead of trying to explain this in words, I'll use a table

Number	Part description	Father	Level
1	A chair	-	0
2	Arm rests	1	1
3	Mechanism	1	1
4	Back	1	1
5	Wood	4	.2
6	Sponge	4	.2
7	Fabric	4	.2
8	Back rest	1	1
9	Wood	8	.2
10	Sponge	8	.2
11	Fabric	8	.2

A chair is built from arm rests, a mechanism (for raising and lowering the seat), a seat and a back rest. For these parts, their father is the chair, or in terms of the above table, part #1. A seat (and a back rest) is made from wood, sponge and fabric; for these parts, their father is the seat (or back rest), part #4 or part #8. With regard to levels, the chair is at level 0; the arm rests, mechanism, seat and back rest are at level 1 (direct sons of the chair); the wood, sponge and fabric are at level 2, which is shown as .2 in the above table.

A customer did not want the level to be displayed as above; instead, he wants that in the field equivalent to level, the arm rests will appear as 1 (no change), the mechanism as 2, the seat as 3, the wood of the seat as 3.1, etc. The table below shows how the customer wants the level to be displayed.

Number	Part description	Father	Level
1	A chair	-	0
2	Arm rests	1	1
3	Mechanism	1	2
4	Back	1	3
5	Wood	4	3.1
6	Sponge	4	3.2
7	Fabric	4	3.3
8	Back rest	1	4
9	Wood	8	4.1
10	Sponge	8	4.2
11	Fabric	8	4.3

This might seem to the customer to be a reasonable request, but in terms of Priority (or any SQL based language) this - at least to me - is extremely difficult to program. It took me half an hour of walking the dog to come up with a solution, but I am not convinced that this is the best/only way of creating those values.

So I am opening this problem as a programming challenge: send me code that achieves what the customer requested.

Some assumptions:

The external program SONRAW has been run, and the exploded BOMs are in a linked copy of PARTARC.

There is a linked table STACK4 whose key is PARTARC.SONACT, i.e. an index (see here for a discussion how SONACT can be used in conjunction with STACK4).

The level string should be stored in STACK4.DOCNO.