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?

First, I will assume that you meant to type:
JOIN SEGMENT_A.PIN IN BEN_ACT TO SEGMENT_B.PIN IN SEGMENT_B AS J0

When you use a sequential file as the cross-ref, you have to ensure that the join field is sorted in the same order as the host. When the file is opened for reading, the pointer will only go from the top of the file to the bottom. If the PIN's are not in the same order, then the pointer has to go back up. WF cannot do that, subsequently the error.

If the SEGMENT_B file is not too big, you might want to hold it to a focus file with an index on PIN, and use the new file in the join.

FILEDEF SEGMENT_B DISK /DATA/FILENAME.prn

TABLE FILE SEGMENT_B
PRINT field1 field2 ... fieldn
BY PIN
ON TABLE HOLD AS TMPSEGF FORMAT FOCUS INDEX PIN
END

JOIN SEGMENT_A.PIN IN BEN_ACT TO TMPSEGF.PIN IN TMPSEGF AS J0

TABLE FILE BEN_ACT
PRINT
PIN
LNAME
END

Thanks for the response. However, because of the way my IS department configured the WF server, I can't use hold files. I have tried. Even the WebFocus tech support can't help.

Can you hold a focus file with multiple indexes? For example, I have DIV-ID and ITEM-ID.

Lee,

Unless your IS department configured WebFOCUS without EDATEMP (and why would anyone do that?), there is no reason you cannot do what dhagen suggested. It is the perfect solution to your problem. The FOCUS hold file will be temporary and will go into EDATEAMP and disappear when your report is done.

quote:

because of the way my IS department configured the WF server, I can't use hold files

I would get that one fixed first. This is MOST BASIC functionality. IF Techsupport can't help, then the problem is not being explained properly. You've either got a space allocation / disk addressing problem or a problem with user privileges and/or security.

Until that problem is fixed, you are EXTREMELY limited in your potential uses of WF.

On further thought, how would you create PDF or Excel files without being able to hold a file to disk. My understanding is that these are created on disk first in tempspace before being delivered to the final destination.

Tee hee, Tom. That one's on me. I never even noticed the date. Darin got scammed as well.

GinnyJakes, Darin Lee and Tom Flynn,

Thank you for all of your input. I am able to index multiple fields for a hold.

ON TABLE HOLD AS HOLD11 FORMAT FOCUS INDEX ITEM_ID DIV_ID