Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Join problem

Join problem

Join problem

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:




Can someone tell me what I'm doing wrong?

RE: Join problem

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.

RE: Join problem

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.  

RE: Join problem

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close