Join problem 1

[FinChase]

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?

 

[focwizard]

Based on the fact that a FILEDEF is needed for SEGMENT_B, I suspect it's a sequential file. If so, then there are THREE rules when JOINing TO a sequential file:

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.

 

[FinChase]

Thanks for your response. I reviewed my files against the info you provided.

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.

 

[focwizard]

Sounds like you're violating rule #2. there are TWO ways to resolve this, depending on which file (host or target) is smaller:

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.