I am doing a join on two fields, one is an employee number, and another is a smart date I have created in a hold file. Is it possible to do this? I am getting blank records when I use both fields when I know there is data in the table I'm trying to look up. When I limit the join to just one field (the employee number), I can pull data, but it is far too many rows (not limited by the date). Am I doing something that can't be done? Here is the code I'm using:

DEFINE FILE WFC_VP_TIMESHTPUNCHV42
SM_SEGDATE/YYMD = HDATE(WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.EVENTDATE,'YYMD');
END
TABLE FILE WFC_VP_TIMESHTPUNCHV42
PRINT
WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.PAYCODENAME
WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.HOURS
WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.LABORLEVELNAME5 AS 'JOB_CODE'
WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.LABORLEVELNAME7 AS 'DEPT'
WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.PERSONID
SM_SEGDATE
BY WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.PERSONNUM
BY WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.EVENTDATE
BY WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.PERSONFULLNAME
BY WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.INPUNCHDTM
BY WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.OUTPUNCHDTM
WHERE WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.PERSONNUM EQ '1083012'
WHERE WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.LABORLEVELNAME2 EQ '96000';
WHERE WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.EVENTDATE GT DT(2018-06-30);
WHERE WFC_VP_TIMESHTPUNCHV42.WFC_VP_TIMESHTPUNCHV42.EVENTDATE LT DT(2018-09-05);
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_PUNCHES FORMAT FOCUS INDEX PERSONID SM_SEGDATE
END

JOIN
LEFT_OUTER HOLD_PUNCHES.SEG01.PERSONID AND HOLD_PUNCHES.SEG01.SM_SEGDATE IN
HOLD_PUNCHES TO UNIQUE WFC_VP_SCHEDULE.WFC_VP_SCHEDULE.PERSONID
AND WFC_VP_SCHEDULE.WFC_VP_SCHEDULE.SHIFTSTARTDATE IN WFC_VP_SCHEDULE TAG J0
AS J0
END
DEFINE FILE HOLD_PUNCHES
EMPLOYEEID/P10=EDIT(J0.WFC_VP_SCHEDULE.PERSONNUM);
DT_STARTHOUR/I2=HPART(J0.WFC_VP_SCHEDULE.SHIFTSTARTTIME, 'HOUR','I2');
DT_STARTMIN/I2 = HPART(J0.WFC_VP_SCHEDULE.SHIFTSTARTTIME, 'MINUTE','I2');
DT_ENDHOUR/I2 = HPART(J0.WFC_VP_SCHEDULE.SHIFTENDTIME, 'HOUR','I2');
DT_ENDMIN/I2 = HPART(J0.WFC_VP_SCHEDULE.SHIFTENDTIME, 'MINUTE','I2');
ALPHA_STARTTIME/A5 = EDIT(DT_STARTHOUR) || ':' || EDIT(DT_STARTMIN);
ALPHA_ENDTIME/A5 = EDIT(DT_ENDHOUR) || ':' || EDIT(DT_ENDMIN);
END
TABLE FILE HOLD_PUNCHES
PRINT
HOLD_PUNCHES.SEG01.SM_SEGDATE
J0.WFC_VP_SCHEDULE.SHIFTSTARTDATE
HOLD_PUNCHES.SEG01.PAYCODENAME
HOLD_PUNCHES.SEG01.HOURS
HOLD_PUNCHES.SEG01.JOB_CODE
HOLD_PUNCHES.SEG01.DEPT
HOLD_PUNCHES.SEG01.DATE_HIRED
HOLD_PUNCHES.SEG01.TERM_DATE
HOLD_PUNCHES.SEG01.SM_PERIOD_START_DATE AS 'PERIOD_START_DATE'
HOLD_PUNCHES.SEG01.SM_PERIOD_END_DATE AS 'PERIOD_END_DATE'
HOLD_PUNCHES.SEG01.COST_CENTER
ALPHA_STARTTIME AS 'SCHEDULE START TIME'
ALPHA_ENDTIME AS 'SCHEDULE END TIME'
BY HOLD_PUNCHES.SEG01.PERSONNUM
BY HOLD_PUNCHES.SEG01.EVENTDATE
BY HOLD_PUNCHES.SEG01.PERSONFULLNAME
BY HOLD_PUNCHES.SEG01.INPUNCHDTM
BY HOLD_PUNCHES.SEG01.OUTPUNCHDTM
WHERE J0.WFC_VP_SCHEDULE.PERSONNUM EQ '1083012';
WHERE WFC_VP_SCHEDULE.WFC_VP_SCHEDULE.SHIFTSTARTDATE GE '20180630';
WHERE WFC_VP_SCHEDULE.WFC_VP_SCHEDULE.SHIFTSTARTDATE LE '20180905';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT XLSX
END

This creates the hold file, but says there are 0 records in the bottom file.

Thanks in advance for any help!

This message has been edited. Last edited by: FP Mod Chuck,

We know HOLD_PUNCHES is a FOCUS file but what kind of table is WFC_VP_SCHEDULE?

As a test can you convert the date field into Alpha and concatenate it to the PERSONID and join on that instead?

To elaborate Babak comment:
- JOINed fields must have the same format and size
- JOINed table must also be from a similar format : FOCUS vs FOCUS, SQL vs SQL

Thanks both of you for your response. WFC_VP_SCHEDULE is an Oracle table. That explains the trouble I'm having. I'll try your suggestion - thanks!