Join Error 1

[kaeserea]

Hello,

I build a join like this:

First I join a hold file with a DB2 table, then I Join the same Hold file with another Hold File:

[tt]
JOIN FIELD1 IN MYNAMES TO FIELD2 IN DB2TABLE AS J1
END
JOIN FIELD3 IN MYNAMES TO ALL FIELD3 IN MYHOLD AS J2
END
[/tt]

(FIELD1 and FIELD2, FIELD3 and FIELD4 have the same format, I checked that with ? HOLD)

I then get the following error:

(FOC1070) VALUE FOR JOIN 'FROM' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED.

I don't quiet understand what that means. Because in a similar join I didn't sort the joning fields neither and that join worked. Can anybody explain?

Regards,
Eva

 

[focwizard]

When you JOIN to a HOLD file (which is a sequential file), you must provide the 'from' values in ascending order, and the order of the 'to' values in the HOLD file must also be in ascending order. This is because, as a sequential file, there's no 'direct access' method, so we go through the file SEQUENTIALLY, and NEVER BACK UP.

So, the error can be from one of several causes:

1. 'FROM' values are not presented in ascending order

2. 'TO' values are not in ascending order

There's also the issue of duplicate values. You can ONLY have duplicates in ONE of the files (either FROM or TO). If in the FROM file, you can ONLY have a UNIQUE JOIN (so we don't have to back up). If duplicates exist in the TO file, you can't have duplicates in the FROM file, as, again, we'd have to back up.

 

[kaeserea]

Hi focwizard,

thanx for the information. I found also out that the from an to field have to be real database fields and not generated by a define like I did. But I will keep your tip in mind the next time I'd like to join two hold files.

Best regards
Eva

 

[focwizard]

Actually, only the 'TO' field needs to be a real database field. The 'FROM' field can be DEFINEd, but you need to use a slightly diferent syntax in the JOIN (using 'WITH realfield'), to indicate that the field is a DEFINE and not a real field.