I am getting a FOC 236 error while trying to join two hold files:

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

Here are the details
====================
The join command :
(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT: DAGTDATA
BYPASSING TO END OF COMMAND

The first HOLD file:
DEFINITION OF HOLD FILE: DAGTDATA
0FIELDNAME ALIAS FORMAT
FOCLIST E01 I5
ACD E02 I11 MISSING = ON
LOGID E03 A200 MISSING = ON
TSTAFFEDTIME E04 D20.2 MISSING = ON
TMEETTIME E05 D20.2 MISSING = ON
TACDCALLS E06 D20.2 MISSING = ON
TDAACDCALLS E07 D20.2 MISSING = ON
TACDTIME E08 D20.2 MISSING = ON
TACWTIME E09 D20.2 MISSING = ON
THOLDTIME E10 D20.2 MISSING = ON
TAVAILTIME E11 D20.2 MISSING = ON
AVAYA_LOGIN E12 A50


Teh Second HOLD file:
DEFINITION OF HOLD FILE: CRIDS
0FIELDNAME ALIAS FORMAT
FOCLIST E01 I5
ACD E02 I11
AGTLOC E03 A30
AVAYA_NAME E04 A50
ACTIVATION_DATE E05 YYMD
REVOCATION_DATE E06 YYMD MISSING = ON
AVAYA_LOGIN E07 A50
TPX E08 A50 MISSING = ON
BRICKVISION E09 A50 MISSING = ON
MAESTRO_TEMPE E10 A50 MISSING = ON
VISION E11 A50 MISSING = ON
MAESTRO_HAWAII E12 A50 MISSING = ON



What am I doing wrong?

We need the JOIN as well.

Remember that the field you are joining TO should be indexed. There are exceptions, but this is the safest way to go.

You can do this when you create the hold file using the syntax

ON TABLE HOLD AS holdfile FORMAT FOCUS INDEX indexfield

The join command is missing, and is in this case essential.
The error that you are getting points to the segment name: DAGTDATA, not to a field.
This might mean that your join statement is not correct.
Maybe you used something like
join dagtdata.dagtdata.acd in dagtdata to dagtdata.dagtdata.acd in crids as j1
This should then be
join dagtdata.dagtdata.acd in dagtdata to crids.crids.acd in crids as j1
Or just leave out the file/segment qualifiers altogether.

Hope this helps ...

Sorry about that! Didn't notice that I had pasted the wrong piece of text......

Here is the JOIN command I am using:

JOIN
CRIDS.SEG01.ACD AND CRIDS.SEG01.AVAYA_LOGIN IN CRIDS TO ALL
DAGTDATA.SEG01.ACD AND DAGTDATA.SEG01.AVAYA_LOGIN IN DAGTDATA AS J0
END

The rror message is ...

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



I tried this one too...

JOIN
CRIDS.CRIDS.ACD AND CRIDS.CRIDS.AVAYA_LOGIN IN CRIDS TO ALL
DAGTDATA.DAGTDATA.ACD AND DAGTDATA.DAGTDATA.AVAYA_LOGIN IN DAGTDATA AS J0
END

but get the following error

(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE:
CRIDS.CRIDS.ACD

It does not appear from your file definitions that your hold files are sorted and/or indexed on the fields you are joining?

Since you are doing a join all, I would define a concatenated field on both files.
DEFINE FILE your input file
ACD_A/A11=EDIT(ACD);
ACD_LOGIN/A61=ACD_A|AVAYA_LOGIN;
END

Then your first by field would be ACD_LOGIN. I would also index this field on DAGTDATA.
Your join would be:

JOIN ACD_LOGIN IN CRIDS TO ALL ACD_LOGIN IN DAGTDATA AS J0

I have this code in place to index the second file in the JOIN

ON TABLE HOLD AS DAGTDATA FORMAT FOCUS INDEX ACD AVAYA_LOGIN

Thanks PBrightwell! That worked.

I am curious to know why the JOIN didn't work when joining on two fields.

Also, This join worked....

JOIN
CRIDS.SEG01.ACD_LOGIN IN CRIDS TO ALL
DAGTDATA.SEG01.ACD_LOGIN IN DAGTDATA AS J0
END

but not this one

JOIN
CRIDS.CRIDS.ACD_LOGIN IN CRIDS TO ALL
DAGTDATA.DAGTDATA.ACD_LOGIN IN DAGTDATA AS J0
END

Your fields were not consecutive in both of your files which tells me that they were not sorted in the same order. That will work on an index file like an ORACLE DB, but it won't work with flat files. Indexing the second file will make this run faster especially since you were doing a one to many join.

did you try ACD_LOGIN IN CRIDS TO ALL ACD_LOGIN IN DAGTDATA?
or
CRIDS.ACD_LOGIN IN CRIDS TO ALL DAGTDATA.ACD_LOGIN IN DAGTDATA?

I think because you are repeating the file id in CRIDS.CRIDS that focus isn't recognizing it

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

quote:

N TABLE HOLD AS DAGTDATA FORMAT FOCUS INDEX ACD AVAYA_LOGIN

I would suggest to use shorter field names when used for index fields.
and two fields will not work

From the manual:

Syntax: How to Join Real Fields
The following JOIN syntax requires that the fields you are using to join the files are real
fields declared in the Master File. This join may be a simple one based on one field in each
file to be joined, or a multi-field join for data sources that support this type of behavior. The
following syntax describes the simple and multi-field variations:
JOIN field1 [AND field1a...] IN host [TAG tag1]
TO [ALL] field2 [AND field2a...] IN crfile [TAG tag2] [AS joinname]
END
where:
JOIN field1
Is the name of a field in the host file containing values shared with a field in the
cross-referenced file. This field is called the host field.
AND field1a...
Can be an additional field in the host file, with the caveats noted below. The phrase
beginning with AND is required when specifying multiple fields.
• When you are joining two FOCUS data sources you can specify up to four
alphanumeric fields in the host file that, if concatenated, contain values shared
with the cross-referenced file. You may not specify more than one field in the
cross-referenced file when the suffix of the file is FOC. For example, assume the
cross-referenced file contains a phone number field with an area
code-prefix-exchange format. The host file has an area code field, a prefix field, and
an exchange field. You can specify these three fields to join them to the phone
number field in the cross-referenced file. The JOIN command treats the three fields
as one. Other data sources do not have this restriction on the cross-referenced file.
• For data adapters that support multi-field and concatenated joins, you can specify
up to 16 fields. See your data adapter documentation for specific information
about supported join features. Note that FOCUS data sources do not support these
joins.
IN host
Is the name of the host file.
TAG tag1
Is a tag name of up to eight characters (usually the name of the Master File), which is
used as a unique qualifier for fields and aliases in host files.
The tag name for the host file must be the same in all the JOIN commands of a joined structure.

You can't use two fields in the join in the cross reference file if the cross reference file is a FOCUS database. Do a search on this in FocalPoint this topic was covered quite extensively not too long ago.

The original hold files were not FOCUS (.FOC) databases.