I have joined two tables (outer left join) and also have defined a date field (current date - 15 days). The date field does not appears on the result join on segments from the host file which are missing a match in the cross reference file. A sample of my code is here:
SET ALL = ON
JOIN T@TTFL04.TTFCO AND T@TTFL04.TTFBR AND T@TTFL04.TTFDV AND T@TTFL04.TTFYY
AND T@TTFL04.TTFILE AND T@TTFL04.TTFSFX IN T@TTFL04 TO ALL CXL003.CXCOCD
AND CXL003.CXBRCD AND CXL003.CXDVCD AND CXL003.CXFLYY AND CXL003.CXFILE AND CXL003.CXFSFX IN CXL003 AS J2
END
SET ASNAMES=ON
SET PAGE=NOPAGE

DEFINE FILE T@TTFL04
TODAY/I8MDYY=&MDYY;
DNEWDATE/MDYY=TODAY;
CURRDATE/A8MDYY=DNEWDATE;
CURRY/A4=EDIT(CURRDATE,'$$$$9999');
CURRM/A2=EDIT(CURRDATE,'99');
CURRD/A2=EDIT(CURRDATE,'$$99');
CURRDTCYM/A6=CURRY | CURRM;
LASTMTHDATE/MDYY=DATEADD (DNEWDATE, 'D', -14.999);
LASTMTHDATE1/A8MDYY=LASTMTHDATE;
LASTMT/A2=EDIT(LASTMTHDATE1, '99');
LASTMTHYR/A4=EDIT(LASTMTHDATE1, '$$$$9999');
LASTDY / A2=EDIT(LASTMTHDATE1, '$$99');
LASTYRMTH/A8=LASTMTHYR | LASTMT | LASTDY;

Here are my results:

LASTYRMTH HBL# Container#
20050204 050255535636 CPSU 4212037
20050204 050255536025 INBU 5006654
20050204 050255536025 ITAU 4201731
20050204 050255536034 INBU 5006654
20050204 050255536034 ITAU 4201731
20050204 050275543347 CPSU 4056926
*Cell is blanks 08009519075
*Cell is blanks 10108725
20050204 105623001493 CLHU 2269897


What am I doing wrong?

Have you tried adding a with clause on the defined date so it knows to create it for every record in the first table.

No. I am not too experienced with WebFocus. How would you do that?

DEFINE FILE T@TTFL04
TODAY/I8MDYY WITH XXXXX =&MDYY;
DNEWDATE/MDYY=TODAY;
CURRDATE/A8MDYY=DNEWDATE;

WHERE XXXX is the name of a key field in T@TTFL04. This tells FOCUS when to define the field and any fields that use it. Also, I tend to avoid the TODAY as a name as it is also a function in FOCUS.

Dear Leah!!

That took care of my issue. I also took your advise and will no longer use TODAY to define fields.

Thanks a lot!!

Glad to hear it worked. Sometimes it's the simple things that get you.

Have a good week.