A safe method of extracting 'son' parts from a bill of materials

I am often asked to prepare a type of report that requires descending through a part's bill of materials and doing something with the 'son' parts, for example showing the cost of raw materials for each part and the cost of work for that part. Such reports (or more correctly, the procedure that prepares the data to be shown in the report) use the external SONRAW procedure along with temporary PART and PARTARC tables. It also happens that the data for each primary part is to be stored separately; for example, such a report showing raw materials for an order could display the data in two different ways: either all the parts required for the complete order, or all the parts required for each line in the order. In the first case, SONRAW would be called once for the order, whereas the second case might require SONRAW to be called in a loop.

I would start such a procedure with the lines

LINK PART TO :$.PRT;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK PARTARC TO :$.ARC;
ERRMSG 1 WHERE :RETVAL <= 0;

Then parts would be entered into the linked table in the following manner

INSERT INTO PART (PART, PARTNAME, FATQUANT)
SELECT ORIG.PART, ORIG.PARTNAME, REALQUANT (SUM (ORDERITEMS.QUANT))
FROM ORDERITEMS, PART ORIG
WHERE ORDERITEMS.PART = ORIG.PART
AND ORDERITEMS.ORD = :ORD
AND ORIG.TYPE = 'P'
GROUP BY 1, 2;
EXECUTE SONRAW :$.PRT, SQL.DATE8, :$.ARC, 0, '-N';

The above example assumes that the code is run in a loop, each time for a different order (represented by :ORD). After SONRAW, something would be done with the 'exploded tree' that is stored in the linked PARTARC table.

So far, so good. So where is the problem? It would often happen that I would add two lines before the 'INSERT INTO PART' statement -

DELETE FROM PART;
DELETE FROM PARTARC;

These are linked tables, right? And so the deletion takes place on these linked tables. And yet somehow, whilst running a procedure that uses this code yesterday, the real PART and PARTARC tables were deleted! I have run such code thousands of times and there has never been a problem with it, but something must have happened to prevent the linkage and so the deletion removed the real tables. 'Fortunately' this was at the beginning of the day, so the previous day's backup was sufficient to restore the missing data.

As this is the second time in a month that I have managed to delete real tables, I am now trying to adopt the attitude that the DELETE statement MUST NEVER BE USED!!! So how can I ensure that I have empty temporary tables before the insertion of new parts? By using UNLINK and REMOVE:

UNLINK AND REMOVE PART;
UNLINK AND REMOVE PARTARC;
LINK PART TO :$.PRT;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK PARTARC TO :$.ARC;
ERRMSG 1 WHERE :RETVAL <= 0;
INSERT INTO PART (PART, PARTNAME, FATQUANT)
SELECT ORIG.PART, ORIG.PARTNAME, REALQUANT (SUM (ORDERITEMS.QUANT))
...

This looks like code waiting to be optimised - surely unlinking and then relinking a table will take time - but this is safe code, something much more important than saving a few seconds (as opposed to shutting down a company for several hours whilst data is restored - I claim that I'm checking the disaster recovery procedure).

What does the SDK have to say on this topic? Use the AND REMOVE option if you wish the linked file to be deleted when it is unlinked. This is necessary when working with loops, particularly when manipulations are carried out on the data in the linked file. If you do not remove the linked file, and the function using LINK and UNLINK is called more than once, you will receive the same copy of the table during the next link. So, if you want the LINK command to open a new (updated) copy of the table, use UNLINK AND REMOVE. That's fairly clear for a change. So as modern day Americans say (in English that would have thrown me out of school), "my bad".

NFILE: advantages and disadvantages

I am sure that I have written about NFILE at some time but I can't find the reference and so I'll start from the beginning. [Edit: I found the entry which now has the NFILE label]

Let's say that one has a procedure that accepts a list of customers as its input - let's even say that it's the HTML document that I worked on yesterday. When one enters a list of customers, the linked customers table will include only those customers, obviously. When one writes * as the input, all the customers from the unlinked customer table have to be copied into the linked customers table; this takes time! 

Enter NFILE: this was introduced in version 17 IIRC. The documentation says: Select NFILE if the linked file comprises a group of records and you want the link table to remain empty when the user enters * or leaves the field empty. How does one use this? By writing code as follows

LINK CUSTOMERS TO :$.CST;  /* NFILE */
ERRMSG 1 WHERE :RETVAL <= 0;
GOTO 1 FROM CUSTOMERS WHERE CUST > 0;
UNLINK CUSTOMERS;
LABEL 1;

This saves time in copying records from the unlinked table to the linked table. Tables that benefit from this are PART, DOCUMENTS, INVOICES and CUSTOMERS. Maybe a few others.

Now back to the HTML document that I described yesterday. I realised that I could filter unwanted customers using a cursor and a series of expressions such as

GOTO 999 WHERE NOT EXISTS (SELECT 1 FROM BRANCHES
WHERE BRANCH > 0);
LINK CUSTOMERS TO :$.CST;
LINK BRANCHES TO :$.BRN;
DECLARE C15 CURSOR FOR
SELECT CUST FROM CUSTOMERS
WHERE CUST > 0;
OPEN C15;
GOTO 999 WHERE :RETVAL <= 0;
LABEL 100;
FETCH C15 INTO :CST;
GOTO 800 WHERE :RETVAL <= 0;
GOTO 200 WHERE :$.A3 <> 'Y'; /* No need to check price quotes if not wanted */
:HASDATA = 0;
SELECT 1 INTO :HASDATA
FROM CPROF, CPROFA, BRANCHES
WHERE CPROF.PROF = CPROFA.PROF
AND CPROFA.BRANCH = BRANCHES.BRANCH
AND CPROF.CUST = :CST
AND CPROF.PDATE >= :$.DAT;
LOOP 100 WHERE :HASDATA = 1;
....
LABEL 700;
/* If we are here, then the customer has failed all the tests
so delete it from the linked table */
DELETE FROM CUSTOMERS WHERE CUST = :CST;
LOOP 100;
LABEL 800;
...

This code is repeated, once for each type of record - sales opportunities, price quotes, orders and projects. The first two lines check whether any branches were passed as a parameter: there's no point in checking anything if there are no branches. I added all the necessary code, ran the program using one specific branch without designating customers: the procedure took a few minutes to sort itself out then showed the output: as designed, only customers with data from the chosen branch were shown.

I wondered how I could improve the procedure's speed - the obvious step would be to define CUSTOMERS as type NFILE instead of FILE. There would be no need to copy customers to the linked table which would save time. So I added this change, ran the program ... and then realised with a shudder that the DELETION WAS NOT FROM THE LINKED TABLE BUT FROM THE REAL TABLE!!! The first code snippet that I showed unlinks CUSTOMERS; thus the DELETE statement does not work on the linked table but rather the real table.

I reached for the power switch (figuratively) as soon as possible but it was too late. A few minutes later, someone called to ask whether customers had disappeared. My code has managed to delete about 16,000 customers out of 24,000 in a few minutes. Straightaway I phoned the network manager who took Priority off line, and then we turned to our database administrator who started restoring the damaged table.

Bottom line: be wary of NFILE! Normally it can help but sometimes it is dangerous.