Good morning,

I am trying to join two hold files together. The hold files are defined as follows:

TABLE FILE HOLDALL
SUM
LONG
LONG_COUNT
SHORT
SHORT_COUNT
BY COMMODITY_CODE
BY CONTRACT_MONTH
BY CONTRACT_YEAR
BY STRIKE
ON TABLE SET ASNAMES ON
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD AS HOLDNONCOMM FORMAT ALPHA
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS OFF
END

TABLE FILE OPEN_INTEREST_SUMM_FIRM_VR
SUM
BEGIN_OPEN_INTEREST
END_OPEN_INTEREST
BEGIN_OPEN_INTEREST_SHORT
END_OPEN_INTEREST_SHORT
BY COMM_CODE_EXCH
BY CONTRACT_MONTH
BY CONTRACT_YEAR
BY EXERCISE_PRICE
&FUT.EVAL WHERE OPTION_TYPE EQ 'F';
&OPT.EVAL WHERE (OPTION_TYPE EQ 'P') OR (OPTION_TYPE EQ 'C');
&PUT.EVAL WHERE OPTION_TYPE EQ 'P';
&CAL.EVAL WHERE OPTION_TYPE EQ 'C';
WHERE DATE_IMPORTED GE DT(&START_DATE);
WHERE DATE_IMPORTED LE DT(&END_DATE);
ON TABLE SET ASNAMES ON
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD AS OIHOLD FORMAT ALPHA
ON TABLE SET HTMLCSS OFF
END

and this is my join:

JOIN
HOLDNONCOMM.HOLDNONCOMM.COMMODITY_CODE AND HOLDNONCOMM.HOLDNONCOMM.CONTRACT_MONTH
AND HOLDNONCOMM.HOLDNONCOMM.CONTRACT_YEAR AND HOLDNONCOMM.HOLDNONCOMM.STRIKE IN HOLDNONCOMM
TO OIHOLD.OIHOLD.COMM_CODE_EXCH AND OIHOLD.OIHOLD.CONTRACT_MONTH
AND OIHOLD.OIHOLD.CONTRACT_YEAR AND OIHOLD.OIHOLD.EXERCISE_PRICE IN OIHOLD AS J9
END

I can tell that both hold files are returning properly, the first has 3 records, the 2nd 245. I am getting the error:

(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE:
HOLDNONCOMM.HOLDNONCOMM.COMMODITY_CODE

Does anyonne have any suggestions/advice on this?

Thanks in advance!

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

Isn't there a known issue surrounding multiple fields used in join expressions? I recall having to concatenate fields to one unique one and joining that from file to file. That sound familiar to anyone?

- ABT

Yeah, AFAIK that's not supported for FOCUS-files. If you need that functionality, apparently XFOCUS-files can do that, or you DEFINE a surrogate key by concatenating the key fields together. With the latter solution, I prefer to put some kind of separator in between the key fields, but that's not a requirement.

This should be OK for non-FOCUS db HOLD files. I would try a JOIN without all those qualifiers - are you sure the segment name in the HOLD file is HOLDNONCOMM?

Thanks you all for the replies.

This updated join worked, as per Francis' suggestion:

JOIN
HOLDNONCOMM.COMMODITY_CODE AND HOLDNONCOMM.CONTRACT_MONTH
AND HOLDNONCOMM.CONTRACT_YEAR AND HOLDNONCOMM.STRIKE IN HOLDNONCOMM
TO OIHOLD.COMM_CODE_EXCH AND OIHOLD.CONTRACT_MONTH
AND OIHOLD.CONTRACT_YEAR AND OIHOLD.EXERCISE_PRICE IN OIHOLD AS J9
END