Hi,
I've got a fex that has worked for months and stopped working yesterday. It is because of a join. I get the following error message:

(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT: EDFEESII
BYPASSING TO END OF COMMAND

When I try to run this:

TABLE FILE DSRINDCS1
SUM COL01S00A COL02S00A COL03S00A COL04S00A COL05S00A COL06S00A COL07S00A COL08S00A COL09S00A COL10S00A COL11S00A COL12S00A COL13S00A COL14S00A COL15S00A
COL16S00A
BY VLEVEL
BY NEWDATE
BY MONTH
ON TABLE HOLD AS DSRINDCS2 FORMAT FOCUS INDEX VLEVEL NEWDATE
END

TABLE FILE EDFEES
PRINT ECDFEE NUMED EDFEE
BY VLEVEL
BY NEWDATE
ON TABLE HOLD AS EDFEESII FORMAT FOCUS INDEX VLEVEL NEWDATE
END

JOIN
LEFT_OUTER VLEVEL AND NEWDATE IN DSRINDCS2 TO VLEVEL AND NEWDATE IN EDFEESII AS JEDFEES
END

TABLE FILE DSRINDCS2
PRINT COL01S00A COL02S00A COL03S00A COL04S00A COL05S00A COL06S00A COL07S00A COL08S00A COL09S00A COL10S00A COL11S00A COL12S00A COL13S00A COL14S00A COL15S00A
COL16S00A EDFEE MONTH
BY VLEVEL
BY NEWDATE
-*ON TABLE HOLD AS DSRINDCS3
END

This message has been edited. Last edited by: Mark1,

Mark,

If VLEVEL and NEWDATE are aliases, they are not carried over to the HOLD file. Use the real column name, not the alias.

To verify this, run this command:

?FF DSRINDCS1

The first column is the real name, the second is the alias - they may be the same or different.

Everything checks out there:

FILENAME= DSRINDCS1
VLEVEL E01 A100
MONTH E02 MTRYY
NEWDATE E03 YYMD
FILENAME= DSRINDCS2
VLEVEL E01 A100
NEWDATE E02 YYMD
MONTH E03 MTRYY
FILENAME= EDFEES
VLEVEL E01 A100
NEWDATE E02 YYMD
FILENAME= EDFEESII
VLEVEL E01 A100
NEWDATE E02 YYMD

One more thing that I just noticed... when I do a print * on EDFEESII, a field called FOCLIST that is not in any of my hold files anywhere! Does anybody know why WF puts a field in my hold file like that?

Another thing I just noticed is that when I join on one field it works fine. When I try to join on two fields it errors out.

This message has been edited. Last edited by: Mark1,

It's because you did a PRINT. It forces uniqueness for each row. You don't have to worry about it.

Another thing I just noticed is that when I join on one field it works fine. When I try to join on two fields it errors out.

Duh! Should have realized that! Oddly enough, only one field can be joined for FOCUS DB! Multiple columns can be joined for flat files or DBMS tables.

One solution is to define a field that is a concatenation of the two indexed fields, if of course, both are alpha or integer.

Another solution is to create flat files with the data sorted in the correct order.

Does not work:

?FF CAR

TABLE FILE CAR
SUM 
SALES
COMPUTE CC/A100 = COUNTRY | CAR;
BY COUNTRY
BY CAR
BY BODYTYPE
ON TABLE HOLD AS H001 FORMAT FOCUS INDEX COUNTRY CAR
END

TABLE FILE CAR
SUM 
FUEL_CAP
COMPUTE CC/A100 = COUNTRY | CAR;
BY COUNTRY
BY CAR
ON TABLE HOLD AS H002 FORMAT FOCUS INDEX COUNTRY CAR
END

?FF H001
?FF H002

JOIN LEFT_OUTER COUNTRY AND CAR IN H001 TO COUNTRY AND CAR IN H002 AS J1

TABLE FILE H001
PRINT 
H001.COUNTRY
H001.BODYTYPE
END

Works:

?FF CAR

TABLE FILE CAR
SUM 
SALES
COMPUTE CC/A100 = COUNTRY | CAR;
BY COUNTRY
BY CAR
BY BODYTYPE
ON TABLE HOLD AS H001 FORMAT FOCUS INDEX CC
END

TABLE FILE CAR
SUM 
FUEL_CAP
COMPUTE CC/A100 = COUNTRY | CAR;
BY COUNTRY
BY CAR
ON TABLE HOLD AS H002 FORMAT FOCUS INDEX CC
END

?FF H001
?FF H002

JOIN LEFT_OUTER CC IN H001 TO CC IN H002 AS J1

TABLE FILE H001
PRINT 
SALES
FUEL_CAP
BY COUNTRY
BY CAR
BY BODYTYPE
END

Although, this does not explain

quote:

worked for months and stopped working yesterday

Seriously, it had been working for months and then started to error out yesterday. At least you have given me a good example from the car file to work with. I'm not understanding why when you join on two fields... CRASH! I'm going to have to rewrite a lot of programs if this continues to be the case.

This message has been edited. Last edited by: Mark1,

Not a crash, just out of gas.

Here's some documentation regarding JOIN:

Creating Reporting Applications With Developer Studio > Joining and Merging Data Sources > Creating Multi-Field Joins

I was slightly wrong about the FOCUS multi-field join - two individual fields can be joined from the host file, the cross-referenced file requires the one concatenated field.

I concatenated the two fields I was joining on and used that one field in my join. That seems to work. Thanks!