Join problem
Join problem
(OP)
I am trying to do a left outer join for a segment (SEGMENT_B) that requires a filedef to another segment (SEGMENT_A) that does not require a filedef. The host file will be SEGMENT_A. It is giving me the following error: (FOC1071) VALUE FOR JOIN 'TO' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED
I have no problems joining the files when I use SEGMENT_B as the host. It appears that I have the FILEDEF statement for SEGMENT_B in the wrong place, but every time I move, WebFocus doesn't recognize it.
Here's what I've written so far:
FILEDEF SEGMENT_B DISK /DATA/FILENAME.prn
JOIN SEGMENT_A.PIN IN SEGMENT_A TO SEGMENT_B.PIN IN SEGMENT_B AS J0
TABLE FILE BEN_ACT
PRINT
PIN
LNAME
END
Can someone tell me what I'm doing wrong?
I have no problems joining the files when I use SEGMENT_B as the host. It appears that I have the FILEDEF statement for SEGMENT_B in the wrong place, but every time I move, WebFocus doesn't recognize it.
Here's what I've written so far:
FILEDEF SEGMENT_B DISK /DATA/FILENAME.prn
JOIN SEGMENT_A.PIN IN SEGMENT_A TO SEGMENT_B.PIN IN SEGMENT_B AS J0
TABLE FILE BEN_ACT
PIN
LNAME
END
Can someone tell me what I'm doing wrong?
Talk To Other Members
RE: Join problem
1. the sequential file must be sorted in ASCENDING order on the 'JOINed' field
2. the records read from the HOST file must ALSO be in ascending order (we never back up if the target file is sequential)
3. We only allow duplicate values in ONE of the files. With a 'JOIN TO', you can have duplicates in the HOST; with a 'JOIN TO ALL', you can have duplicates in the TARGET file.
Violating any of these rules can result in your error message.
RE: Join problem
There should be no duplicate values in the either the host file or the sequential file.
The host file is an Informix file that is loaded by my IS department. I don't really know what sort of sorting is used for that, and because it is stored on a part of the reporting server to which I have no access, I can't look at it. The file I am trying to join it to is a text file. I resorted it on the JOINED field in ascending order, and the error message has now changed to the following: (FOC1070) VALUE FOR JOIN 'FROM' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED.
RE: Join problem
1. resort the HOST records. Based on the fact that it's in Informix, that's probably not feasible, unless you're JOINing from an extract, in which case, ensure that the extracted data is sorted correctly
2. Create the TARGET file as a FOCUS file (perhaps through HOLD FORMAT FOCUS), with an INDEX on the target field. It will then have a 'direct access' method (the index), and record order will no longer be relevant.