I am trying to join two com files, each uploaded from Excel, on localhost, all Windows environment;
I need to add calendar info to teh host file.
Structured as follows:
HOST FILE:
FILENAME=TESTHF2, SUFFIX=COM ,
DATASET=FPA1/TESTHF2.TXT, $
SEGMENT=TESTHF2, SEGTYPE=S1, $
FIELDNAME=DISCHGDATE, ALIAS=DISCHGDATE, USAGE=MDYY, $
FIELDNAME=MEDREC, ALIAS=MEDREC, USAGE=D12.2S, $
FIELDNAME=ADMDATE, ALIAS=ADMDATE, USAGE=MDYY, $
FIELDNAME=FNAME, ALIAS=FNAME, USAGE=A50, $
FIELDNAME=LNAME, ALIAS=LNAME, USAGE=A50, $
FIELDNAME=FIELD_6, ALIAS=FIELD_6, USAGE=D12.2S, $
FIELDNAME=HF1, ALIAS=HF1, USAGE=A50, $
FIELDNAME=DSINSTADMD, ALIAS=DSINSTADMD, USAGE=A50, $
FIELDNAME=DSINSTADAC, ALIAS=DSINSTADAC, USAGE=A50, $
FIELDNAME=DSINSTADDI, ALIAS=DSINSTADDI, USAGE=A50, $
FIELDNAME=DSINSTADFU, ALIAS=DSINSTADFU, USAGE=A50, $
FIELDNAME=DSINSTADSW, ALIAS=DSINSTADSW, USAGE=A50, $
FIELDNAME=DSINSTADWT, ALIAS=DSINSTADWT, USAGE=A50, $
FIELDNAME=UNIT, ALIAS=UNIT, USAGE=A50, $
FIELDNAME=HF1_FORM, ALIAS=HF1_FORM, USAGE=A50, $

TARGET FILE (to lookup the calendar data)
FILENAME=CALENDAR, SUFFIX=COM ,
DATASET=FPA1/CALENDAR.TXT, $
SEGMENT=CALENDAR, SEGTYPE=S1, $
FIELDNAME=KEYDATE, ALIAS=KEYDATE, USAGE=MDYY, FIELDTYPE=I, $
FIELDNAME=WEEKOF, ALIAS=WEEKOF, USAGE=A50, $
FIELDNAME=YEAR, ALIAS=YEAR, USAGE=D12.2S, $
FIELDNAME=MONTH_YEAR, ALIAS=MONTH_YEAR, USAGE=A50, $
FIELDNAME=MONTH, ALIAS=MONTH, USAGE=D12.2S, $
FIELDNAME=MONTHW, ALIAS=MONTHW, USAGE=A50, $
FIELDNAME=MM_YYYY, ALIAS=MM_YYYY, USAGE=A50, $
FIELDNAME=QUARTERDATE, ALIAS=QUARTERDATE, USAGE=D12.2S, $
FIELDNAME=XMM_YYYY, ALIAS=XMM_YYYY, USAGE=MDYY, $
FIELDNAME=FIELD_10, ALIAS=FIELD_10, USAGE=D12.2S, $

And the join is:
JOIN
TESTHF2.TESTHF2.DISCHGDATE IN TESTHF2 TO MULTIPLE CALENDAR.CALENDAR.KEYDATE
IN CALENDAR AS J3
END

I've tried a focus hold file with index as in:
ON TABLE HOLD AS DDAD FORMAT FOCUS INDEX 'DISCHGDATE'
then I get 'description cannot be found for file DDAD

If I dont use a hold file, I get
(FOC1101) THIS INTERFACE DOES NOT SUPPORT THE GLOBAL JOIN

See code:
TABLE FILE TESTHF2
PRINT
DISCHGDATE
MEDREC
ADMDATE
FNAME
LNAME
FIELD_6
HF1
DSINSTADMD
DSINSTADAC
DSINSTADDI
DSINSTADFU
DSINSTADSW
DSINSTADWT
UNIT
HF1_FORM
ON TABLE HOLD
END
JOIN
DISCHGDATE IN TESTHF2 TO UNIQUE CALENDAR.CALENDAR.KEYDATE
IN CALENDAR AS J3
END
TABLE FILE TESTHF2
PRINT
YEAR
WEEKOF
HEADING
""
FOOTING
""
[end]

Thank you
Gm

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

I would suggest TABLEing each COM file and HOLDing, as different names, then joining them.

Your code above HOLDs the TESTHF2, but your join does not use it.

TABLE FILE CALENDAR
PRINT ...
ON TABLE HOLD AS HLD_CALN
END
JOIN
DISCHGDATE IN TESTHF2 TO UNIQUE KEYDATE
IN HLD_CA AS J3
END
TABLE FILE TESTHF2
PRINT
YEAR
WEEKOF
HEADING
""
FOOTING
""
[end]

Yes, you are correct. HOLDing both as Format FOCUS with index, allowed the join to work.
thank you

Both don't need to be FOCUS, only the join to file, making sure that the field you are joining to is indexed.

You could also try to use the MATCH FILE command. No need for joins. No need for converting to focus format. The result of the MATCH FILE is a hold file that has all data in it. Sometimes I find this easier to use than JOIN.