Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Stored Procedure Pull dates 2 days from Current

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Stored Procedure Pull dates 2 days from Current
 Login/Join
 
Silver Member
posted
This code pulls the basic information I need but now I need to pull all those active with an apptdate that is 2 days from current date, so they are notified by a phone system. I need to set this up to run automatically like a SQL stored procedure can this be done?

TABLE FILE PATIENT
PRINT
'PATIENT.PATIENT.PATIENT'
'PATIENT.PATIENT.STATUS'
'PATIENT.PATIENT.LAST'
'PATIENT.PATIENT.FIRST'
'PATIENT.PATIENT.HOMEAREA'
'PATIENT.PATIENT.HOMEPHONE'
'APPTHIS.APPTHIS.DATE'
'APPTHIS.APPTHIS.TIME1'
HEADING
""
FOOTING
""
WHERE PATIENT.PATIENT.STATUS EQ 'ACTIVE';


V. 762
Windows XP
 
Posts: 47 | Registered: December 19, 2007Report This Post
Expert
posted Hide Post
-SET &TWODAYS=AYMD(&YYMD,2,'I8YYMD');
TABLE FILE PATIENT
...
WHERE APPTHIS.APPTHIS.DATE EQ &TWODAYS
...
END  


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Silver Member
posted Hide Post
quote:
WHERE APPTHIS.APPTHIS.DATE EQ &TWODAYS
...


I am getting an Error Parsing Report Request

>SET<
&TWODAYS=AYMD(&YYMD,2,'I8YYMD');

I put it in my code like this:

SET &TWODAYS=AYMD(&YYMD,2,'I8YYMD');
TABLE FILE PATIENT
PRINT
'APPTHIS.APPTHIS.TIME1'
'PATIENT.PATIENT.PATIENT'
'PATIENT.PATIENT.STATUS'
'PATIENT.PATIENT.LAST'
'PATIENT.PATIENT.FIRST'
'PATIENT.PATIENT.HOMEAREA'
'PATIENT.PATIENT.HOMEPHONE'
BY 'APPTHIS.APPTHIS.DATE'
HEADING
""
FOOTING
""
WHERE PATIENT.PATIENT.STATUS EQ 'ACTIVE';
WHERE APPTHIS.APPTHIS.DATE EQ &TWODAYS;



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


V. 762
Windows XP
 
Posts: 47 | Registered: December 19, 2007Report This Post
Expert
posted Hide Post
That's beacuase you missed out the all important hyphen to indicate that it's a DM statement. The hyphen is there in Ginny's post.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Silver Member
posted Hide Post
I added the hyphen and I am still getting the parsing error only now it looks like:

>-<
-SET &TWODAYS=AYMD(&YYMD,2,'I8YYMD');

Thanks,


V. 762
Windows XP
 
Posts: 47 | Registered: December 19, 2007Report This Post
Master
posted Hide Post
What is the format of APPTHIS.APPTHIS.DATE?




Scott

 
Posts: 865 | Registered: May 24, 2004Report This Post
Silver Member
posted Hide Post
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?


V. 762
Windows XP
 
Posts: 47 | Registered: December 19, 2007Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Silver Member
posted Hide Post
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, $


V. 762
Windows XP
 
Posts: 47 | Registered: December 19, 2007Report This Post
Expert
posted Hide Post
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Silver Member
posted Hide Post
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.


V. 762
Windows XP
 
Posts: 47 | Registered: December 19, 2007Report This Post
Expert
posted Hide Post
Do you have a multiple line JOIN? If so have you put the requisite END statement?

That's the normal failure!

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Silver Member
posted Hide Post
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


V. 762
Windows XP
 
Posts: 47 | Registered: December 19, 2007Report This Post
Expert
posted Hide Post
Try commenting out all the lines in your fex and add them back one at a time till you find the one that fails.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Silver Member
posted Hide Post
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


V. 762
Windows XP
 
Posts: 47 | Registered: December 19, 2007Report This Post
Expert
posted Hide Post
-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
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Silver Member
posted Hide Post
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!


V. 762
Windows XP
 
Posts: 47 | Registered: December 19, 2007Report This Post
Silver Member
posted Hide Post
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


V. 762
Windows XP
 
Posts: 47 | Registered: December 19, 2007Report This Post
Silver Member
posted Hide Post
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


V. 762
Windows XP
 
Posts: 47 | Registered: December 19, 2007Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Stored Procedure Pull dates 2 days from Current

Copyright © 1996-2020 Information Builders