| Title: | FOCUS, from INFORMATION BUILDERS |
| Moderator: | ZAYIUS::BROUILLETTE |
| Created: | Thu Feb 19 1987 |
| Last Modified: | Mon May 05 1997 |
| Last Successful Update: | Fri Jun 06 1997 |
| Number of topics: | 615 |
| Total number of notes: | 1779 |
Here's a challenge for all you FOCUS experts. I have a BOM (Bill
of Materials) file that contains parts (field name PART) and their
children (field name L2_PART) and _their_ children (field name
L3_PART) and so on.
I also have a PARTS file, which contains part-numbers (field name
PART), their descriptions (field name DESC) and status information.
My client would like a report that shows PART plus its description and
status codes, and also L2_PART and its description and status code.
So far I've talked with no one here who knows:
(1) how to do that at all, be it in a single step (i.e., with 2 JOIN
statements) or in multiple steps (by using a HOLD file) or
(2) if one could do it, how, when writing a report, one would
distinguish between PART's description and status and L2_PART's
description and status.
I'd think that two join statements ought to do the trick for the first
part, for example:
JOIN PART IN BOM TO PART IN PARTS AS A
JOIN L2_PART IN BOM TO PART IN PARTS AS B
... but
rumor has it that the second JOIN will supercede the first one. In any
case, one is still left with keeping the two DESC fields referring to
the appropriate field in BOM.
If the foregoing isn't clear and you speak DATATRIEVE, I believe it
would code as follows:
FOR A IN BOM
FOR B IN PARTS WITH B.PART=A.PART
FOR C IN PARTS WITH C.PART=A.L2_PART
... or if you're into
eating machine cycles, and you've given PART in PARTS a convenient
query-name of, say, L2_PART ...
REPORT BOM CROSS B IN PARTS OVER PART CROSS C IN PARTS OVER L2_PART
In either case, you'd refer to PART's description as B.DESCRIPTION and
L2_PART's description as C.DESCRIPTION.
If there's a note on this subject, I was unable to find it. I'll be
happy to research past discussions of this modestly obscure requirement.
Thanks in advance.
Donald Reinke
Software Manufacturing
| T.R | Title | User | Personal Name | Date | Lines |
|---|---|---|---|---|---|
| 239.1 | Recursive join | MEMV02::VOSS | Mon Jul 17 1989 16:11 | 18 | |
I have worked on this problem in a couple of ways and I can think
of another I have not tried.
The first way I worked with similiar problem was with a 'recursive
join'. It was a while ago but it did work and reference to this
can be found on pages 4-49 to 4-54, Section 3.5 Complex File Structures
of the new FOCUS VMS User Manual.
A way to address to field in two files that have the same field
name when you use a join is to rename the field in a alternate MASTER
file description and use that. You then can compare the values in
those two fields without doing a MATCH.
Which is the way I haven't tried but it should work though it can lead
to multiple MATCHes and therefore more processing.
I hope this helps.
Greg
| |||||
| 239.2 | What we do is ... | EFGV04::LEE | Tue Jul 18 1989 10:16 | 78 | |
The 2 joins can be in effect simultaneously because you have given
them a different name each, however, you DO have the problem of
distinguishing the two DESC fields.
An alternative approach, the one we use (although it is heavy on extra
CPU) is:
JOIN PART IN BOM TO PART IN PARTS AS A
DEFINE FILE BOM
L1_DESC = DESC;
END
TABLE FILE BOM
PRINT field-1-BOM
AND field-2-BOM
AND field-3-BOM
...
AND PART
AND L1_DESC
AND L2_PART
...
AND field-N-BOM
ON TABLE HOLD AS TEMP1
END
JOIN L2_PART IN TEMP1 TO PART IN PARTS AS B
DEFINE FILE TEMP1
L2_DESC = DESC; <----- Note that there is no ambiguity here as
END the field DESC is not held in TEMP1, so
it must be from the JOIN and thus must
be a DESC for L2_PART.
TABLE FILE TEMP1
PRINT field-1-BOM
AND field-2-BOM
AND field-3-BOM
...
AND PART
AND L1_DESC
AND L2_PART
AND L2_DESC
...
AND field-N-BOM
ON TABLE HOLD AS TEMP2
END
This can be continued indefinitely, 1 JOIN each time, and 'renaming'
the DESC field.
Words of * W * A * R * N * I * N * G :
------------------------------------
1. Often when you MATCH fields of *.FTM (with corresponding *.MAS
produced) from an ON TABLE HOLD or AFTER MATCH HOLD, the MATCH goes
drastically wrong if the *.MAS has values E%% in the ALIAS field.
Our solution is a standard *.COM which replaces E%% with " "
(3 blanks) on every *.MAS produced by FOCUS HOLD's. (These MAS's
seem to have a predetermined format which does not vary. We pass in
a parameter with the name of the MAS, it looks in the default
directory for it ...etc). You are quite welcome to have a copy of
this if you would like.
Alternatively, if the elements of the HOLD file are relatively stable
- ie. the fields / field sizes don't change regularly, then use ON
TABLE HOLD once to get the *.MAS, then hand-edit it to take out the
E%%, and in your production FEX, use ON TABLE SAVE.
2. This solution works best if you use SET HOLD = ALPHA. Otherwise the
hold files created will all be padded to the lowest multiple of
4-bytes. ie. 1-,2-,3,-4-character fields will be saved as 4-char fields,
5-,6-,7-,8-character fields will be saved as 8-char fields etc
Hope this helps.
Regards, Sim Lee.
| |||||
| 239.3 | Thanks -- All is Well | SKYE::REINKE | Software Manufacturing Technologies | Tue Aug 08 1989 09:47 | 12 |
Thanks to you and the author of .1 The solution in .2 is the way that
eventually worked for us. Our DBA says the solution in .1 would not
work because (I think I have this right) the BOM file is already a
joined file.
Anyway, it CAN be done in FOCUS. (Apologies to VW)
Regards,
Donald Reinke
Software Manufacturing
| |||||