I am wondering, I am new to this so it may just be ignorance on my part; but I recall using a method on birthdates that required me to first convert my date format to a Smart Date could this be the problem here? Do I need to first convert the date then add Ginny's logic?

Since I don't know what your data source type is and assuming it is relational, I would convert &TWODAYS so that it matches the format of the date in your data base and compare against that.

If you do the define and convert the date to smart date, then the WHERE won't get passed to the backend relational engine resulting in an inefficient request.

Please look in the master for PATIENT and tell us how the date is defined. We can help you better that way.

I copied this from the ApptHis.mas:

FILENAME=APPTHIS, SUFFIX=SQLORA , $
SEGMENT=APPTHIS, SEGTYPE=S0, $
FIELDNAME=APPTHIS, ALIAS=ApptHis, USAGE=P11, ACTUAL=P6, $
FIELDNAME=APPOINTMENT, ALIAS=Appointment, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=DATE, ALIAS=Date, USAGE=YYMD, ACTUAL=DATE,
MISSING=ON, $
FIELDNAME=CHAIR, ALIAS=Chair, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=PATIENT, ALIAS=Patient, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=TYPE, ALIAS=Type, USAGE=P6, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=PRODUCER, ALIAS=Producer, USAGE=A10, ACTUAL=A10,
MISSING=ON, $
FIELDNAME=FROM, ALIAS=From, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=CREATEDATE, ALIAS=CreateDate, USAGE=YYMD, ACTUAL=DATE,
MISSING=ON, $
FIELDNAME=CODE, ALIAS=Code, USAGE=A15, ACTUAL=A15,
MISSING=ON, $
FIELDNAME=UNIT0, ALIAS=Unit0, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=UNIT1, ALIAS=Unit1, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=UNIT2, ALIAS=Unit2, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=UNIT3, ALIAS=Unit3, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=UNIT4, ALIAS=Unit4, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=UNIT5, ALIAS=Unit5, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=TIME0, ALIAS=Time0, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=TIME1, ALIAS=Time1, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=TIME2, ALIAS=Time2, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=TIME3, ALIAS=Time3, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=TIME4, ALIAS=Time4, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=TIME5, ALIAS=Time5, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=ESTIMATE, ALIAS=Estimate, USAGE=D20.2, ACTUAL=D8,
MISSING=ON, $
FIELDNAME=CLASS, ALIAS=Class, USAGE=A15, ACTUAL=A15,
MISSING=ON, $
FIELDNAME=MODIFIED, ALIAS=Modified, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=USER, ALIAS=User, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=ISRECALL, ALIAS=IsRecall, USAGE=P6, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=ISASAP, ALIAS=IsAsap, USAGE=P6, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=ISCONFIRM, ALIAS=IsConfirm, USAGE=P6, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=ISSHORTNOTICE, ALIAS=IsShortNotice, USAGE=P6, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=STATUS, ALIAS=Status, USAGE=P6, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=REQUEST, ALIAS=Request, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=RANK, ALIAS=Rank, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=USTATUS, ALIAS=UStatus, USAGE=A6, ACTUAL=A6,
MISSING=ON, $
FIELDNAME=RESERVE, ALIAS=Reserve, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=ORTHOSTAGE, ALIAS=OrthoStage, USAGE=A6, ACTUAL=A6,
MISSING=ON, $
FIELDNAME=ISORTHO, ALIAS=IsOrtho, USAGE=P6, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=CANCEL, ALIAS=Cancel, USAGE=A6, ACTUAL=A6,
MISSING=ON, $
FIELDNAME=REASON, ALIAS=Reason, USAGE=A200V, ACTUAL=A200V,
MISSING=ON, $
FIELDNAME=ASSISTANT, ALIAS=Assistant, USAGE=A10, ACTUAL=A10,
MISSING=ON, $
FIELDNAME=PTRECALL, ALIAS=PtRecall, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=CLINIC, ALIAS=Clinic, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=ISREQUEST, ALIAS=IsRequest, USAGE=P6, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=INSTRUCTOR, ALIAS=Instructor, USAGE=P11, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=ISNEW, ALIAS=IsNew, USAGE=P6, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=CHECKINTIME, ALIAS=CheckInTime, USAGE=P11, ACTUAL=P8,
MISSING=ON, $

One thing I would check is the syntax immediately before the "-SET" to ensure that you haven't missed something.

For WF to complain about the hyphen normally means preceding syntax.

T

The table join syntax was before the Set so I took the Set Syntax and put it before the table join syntax and Now I am getting this error:

0 ERROR AT OR NEAR LINE 22 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC002) A WORD IS NOT RECOGNIZED: ...
BYPASSING TO END OF COMMAND

I also tried putting END right under the ...
following my where codes:

WHERE PATIENT.PATIENT.STATUS EQ 'ACTIVE';
WHERE APPTHIS.APPTHIS.DATE EQ &TWODAYS;

still get an error.

Do you have a multiple line JOIN? If so have you put the requisite END statement?

That's the normal failure!

T

This is how I began my Code there is a multiple Join but I have an End after the join and before my Table File Patient Do I need it elsewhere?

-SET &TWODAYS=AYMD(&YYMD,2,'I8YYMD');
JOIN
LEFT_OUTER PATIENT.PATIENT.PATIENT IN PATIENT TO MULTIPLE
APPTHIS.APPTHIS.PATIENT IN APPTHIS AS J0
END
TABLE FILE PATIENT

Try commenting out all the lines in your fex and add them back one at a time till you find the one that fails.

This returns the data I had before. I just need to show all appts that will happen within 2 days from the current date. Having commented out I get the data but not with the criteria I need.

JOIN
LEFT_OUTER PATIENT.PATIENT.PATIENT IN PATIENT TO MULTIPLE
APPTHIS.APPTHIS.PATIENT IN APPTHIS AS J0
END
TABLE FILE PATIENT
-*SET &TWODAYS=AYMD(&YYMD,2,'I8YYMD');
-*WHERE APPTHIS.APPTHIS.DATE EQ &TWODAYS;
-*...
PRINT
'APPTHIS.APPTHIS.DATE'
'APPTHIS.APPTHIS.TIME1'
'PATIENT.PATIENT.PATIENT'
'PATIENT.PATIENT.LAST'
'PATIENT.PATIENT.FIRST'
'PATIENT.PATIENT.HOMEAREA'
'PATIENT.PATIENT.HOMEPHONE'
'PATIENT.PATIENT.STATUS'
HEADING
""
FOOTING
""
WHERE PATIENT.PATIENT.STATUS EQ 'ACTIVE';
ON TABLE NOTOTAL
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
$
ENDSTYLE
END

-SET &TWODAYS=AYMD(&YYMD,2,'I8YYMD');
JOIN
LEFT_OUTER PATIENT.PATIENT.PATIENT IN PATIENT TO MULTIPLE
APPTHIS.APPTHIS.PATIENT IN APPTHIS AS J0
END
TABLE FILE PATIENT
PRINT
'APPTHIS.APPTHIS.DATE'
'APPTHIS.APPTHIS.TIME1'
'PATIENT.PATIENT.PATIENT'
'PATIENT.PATIENT.LAST'
'PATIENT.PATIENT.FIRST'
'PATIENT.PATIENT.HOMEAREA'
'PATIENT.PATIENT.HOMEPHONE'
'PATIENT.PATIENT.STATUS'
HEADING
""
FOOTING
""
WHERE PATIENT.PATIENT.STATUS EQ 'ACTIVE';
WHERE APPTHIS.APPTHIS.DATE EQ &TWODAYS;
ON TABLE NOTOTAL
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
$
ENDSTYLE
END
  

I don't understand why this isn't working.

Ginny:

I called tech support and this is how we had to fix it.

JOIN
LEFT_OUTER PATIENT.PATIENT.PATIENT IN PATIENT TO MULTIPLE
APPTHIS.APPTHIS.PATIENT IN APPTHIS AS J0
END
DEFINE FILE PATIENT
TDAY/YYMD = '&DATEYYMD';
END

TABLE FILE PATIENT
PRINT
'APPTHIS.APPTHIS.DATE'
HEADING
""
FOOTING
""
WHERE APPTHIS.APPTHIS.DATE EQ TDAY + 2;
ON TABLE NOTOTAL
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
$
ENDSTYLE
END

Thank you so much for all your help and effort!

This is how the code looks now but when I enter the solution into the code I am still getting the parsing error this time on Join. I have put a call in to re-open the case and will print the code when I have it working correctly.

JOIN
LEFT_OUTER PATIENT.PATIENT.PATIENT IN PATIENT TO MULTIPLE
APPTHIS.APPTHIS.PATIENT IN APPTHIS AS J0
END
DEFINE FILE PATIENT
TDAY/YYMD='&DATEYYMD';
END
TABLE FILE PATIENT
PRINT
'PATIENT.PATIENT.FIRST'
'PATIENT.PATIENT.LAST'
'PATIENT.PATIENT.HOMEAREA'
'PATIENT.PATIENT.HOMEPHONE'
'APPTHIS.APPTHIS.DATE'
'APPTHIS.APPTHIS.TIME1'
'PATIENT.PATIENT.STATUS'
HEADING
""
FOOTING
""
WHERE PATIENT.PATIENT.STATUS EQ 'ACTIVE';
WHERE APPTHIS.APPTHIS.DATE EQ TDAY+2;

ON TABLE NOTOTAL
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
$
ENDSTYLE
END

I got it to work this works but since my appt dates are Mon-Fri I need the Thursday feed to reflect TDAY+4 and Mon-Wed TDAY+2 not sure on how to do this but when I get this figured out I will add to the code to show how it was done.

TABLE FILE PATIENT
PRINT
'PATIENT.PATIENT.FIRST'
'PATIENT.PATIENT.LAST'
'PATIENT.PATIENT.HOMEAREA'
'PATIENT.PATIENT.HOMEPHONE'
'APPTHIS.APPTHIS.DATE'
'APPTHIS.APPTHIS.TIME1'
'PATIENT.PATIENT.STATUS'
HEADING
""
FOOTING
""
WHERE PATIENT.PATIENT.STATUS EQ 'ACTIVE';
WHERE APPTHIS.APPTHIS.DATE EQ TDAY+4;
ON TABLE NOTOTAL
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
$
ENDSTYLE
END

quote:

WHERE APPTHIS.APPTHIS.DATE EQ TDAY+4;

If PATIENT is a relational data base, I do not recommend your solution. I would go back to my original solution which will generate more efficient SQL. If your problem is with the JOIN, then get that fixed.

Using a defined field to test against a data base field disables optimization and the WHERE is not passed.

If PATIENT is not relational, then ignore this post.