| 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 |
I am trying to join two RMS indexed files together. In file1 the field
I wish to join on is part of the primary key (first part of key). In
file2 the field I wish to join on is also part of the primary ket (but
NOT the first part of key).
I get the following error when I do the join :
(FOC1040) 'TO' FIELD IS NOT IN THE BEGINNING OF THE KEY :
PARTS/P_INVOICENO
I tried to define a field equal to the one I wish to join on and use
that field on the join, however I then get the error :
(FOC236) LINKED FILE DOES NOT HAVE MATCHING KEY FIELD OR SEGMENT :
I have since read the Focus manual and it states that to do a join on
a keyed RMS file, the joined field must be the initial part of key.
Is there ANY way around this ? I do not want to use MATCH because the
files are huge.
Any help will be appreciated.
Martin.
| T.R | Title | User | Personal Name | Date | Lines |
|---|---|---|---|---|---|
| 398.1 | Keyed Up | DUCK::READW | Wed Feb 27 1991 14:31 | 27 | |
Martin,
I wonder if you could simply transpose the files in the JOIN statement,
ie. instead of "JOIN X IN A TO Y IN B",
you could use "JOIN Y IN B TO X IN A" -
where field X is the first part of the primary key in file A.
It's late, but this might not present a problem, unless it messed up
some of your other indexes or you had occurrences of field X in file A
that had no matching value of field Y in file B, or vice versa. If so,
your only recourse might well be to take an extract from the second
file in which the field that you wish to JOIN to becomes a key field.
If you WERE allowed to issue a JOIN to a field that was not either
itself a key field or at least the first part of a key field, then the
JOIN could not take advantage of indexed access and the repeated
sequential searches through the second file that this would entail
could cause your query to take forever to run! On the other hand, it
doesn't matter whether the field that you are joining from is a key
or not, since no more than one sequential search through the first
file will be necessary anyway.
Hope this helps,
Warren
| |||||
| 398.2 | DUCK::READW | Thu Feb 28 1991 04:49 | 9 | ||
Re .-1
In addition to simply transposing the files in the JOIN statement, you
may have to use JOIN ... TO ALL ... rather than just JOIN ... TO ... if
reversal means that you are changing from a many-to-one relationship
to a one-to-many.
Warren
| |||||