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     INCORRECT USE OF DATE-TIME FIELD - SOLVED!

Read-Only Read-Only Topic
Go
Search
Notify
Tools
INCORRECT USE OF DATE-TIME FIELD - SOLVED!
 Login/Join
 
Master
posted
I am trying to populate a field with a date/time stamp
based on the presence of data the current and previous
record.

I have posted the code and the ERROR Message below:

quote:

-*
JOIN CLEAR *
JOIN BLAH IN BLAH-BLAH TO SEED IN APPLES AS J1
JOIN ... IN FRUITY TO PITS IN CHERRIES AS J2
-RUN
-*
TABLE FILE HSCLARITY_ADT
PRINT
PAT_ID
PAT_ENC_CSN_ID AS 'CSN,ID'
BILL_NUM
EVENT_ID AS 'EVENT'
HOSP_ADMSN_TIME AS 'ADMIT_TIME'
EFFECTIVE_TIME AS 'EFFECT_TIME'
DEPARTMENT_ID AS 'DEPT_ID'
COMPUTE XDEPT/A20 = GETTOK(DEPARTMENT_NAME,254,1,'-',20,XDEPT); AS 'UNIT'
XFER_EVENT_ID AS 'XFER_OUT'
XFER_IN_EVENT_ID AS 'XFER_IN'
EVENT_TYPE_C AS 'EVENT_TY'
EVENT_SUBTYPE_C AS 'EVENT_STY'
-*
BY PAT_CONTACT_MPI_NO AS 'MRN' SKIP-LINE
BY HOSP_ADMSN_TIME NOPRINT
BY EFFECTIVE_TIME NOPRINT
WHERE BILL_NUM EQ '10037058';
WHERE EVENT_TYPE_C EQ '1' OR '2' OR '3' OR '4';
WHERE EVENT_SUBTYPE_C NE '2';
WHERE EFFECTIVE_TIME GE DT(&BEG1 00:00:00) AND EFFECTIVE_TIME LE DT(&END1 23:59:59);
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE HOLD AS FX1
END
-RUN
-*
?FF FX1
-RUN


FILENAME= FX1
MRN E01 A25
PAT_ID E02 A18
CSN E03 P19
BILL_NUM E04 A25
EVENT E05 P19
ADMIT_TIME E06 HYYMDS
EFFECT_TIME E07 HYYMDS
DEPT_ID E08 P19
DEPARTMENT_NAME E09 A254
UNIT E10 A20
XFER_OUT E11 P19
XFER_IN E12 P19
EVENT_TY E13 I11
EVENT_STY E14 I11

quote:

DEFINE FILE FX1
XFER_IN/HYYMDS MISSING ON = IF (EVENT NE LAST EVENT)
AND EVENT EQ LAST XFER_IN
AND EVENT_TY EQ '3'
THEN EFFECT_TIME
ELSE MISSING;
END
-*
TABLE FILE FX1
HEADING CENTER
"TEST THE TIME STAMPS"
PRINT
XFER_IN
EFFECT_TIME
UNIT
BY MRN
END
-RUN


0 ERROR AT OR NEAR LINE 77 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC36346) INCORRECT USE OF DATE-TIME FIELD OR CONSTANT
BYPASSING TO END OF COMMAND
0 ERROR AT OR NEAR LINE 92 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: XFER_IN
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
-EXIT

Does anyone have any ideas? Frowner

Thanks!

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


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
What do &BEG1 and &END1 look like, i.e. do they have slashes or dashes in them to separate the date parts? What date format is your target data base expecting? Once you figure that out, you should be good to go.


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
Master
posted Hide Post
-SET &BEG1 = '20090528';
-SET &END1 = '20090528';

In the code I have:
quote:

WHERE EFFECTIVE_TIME GE DT(&BEG1 00:00:00) AND EFFECTIVE_TIME LE DT(&END1 23:59:59);


It is interpreted as this:
quote:

WHERE EFFECTIVE_TIME GE DT(20090528 00:00:00) AND EFFECTIVE_TIME LE DT(20090528 23:59:59);


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
At first I thought it had something to do with the MISSING code, but this works:

DEFINE FILE CAR
XFER_IN/HYYMDS MISSING ON = 
IF COUNTRY EQ 'ENGLAND' THEN DT(2009/12/01 10:23:56.000) 
ELSE MISSING;
END

TABLE FILE CAR
PRINT
CAR
XFER_IN
WHERE XFER_IN GT DT(2009/10/10 11:22:03.000)
END

It also works without the .000 milliseconds.

I presume line 77 is the DEFINE of XFER_IN.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Tom, depending on your backend data source, you have to insert slashes or dashes between the date parts to get this to work. Try one then the other till you get the right combo. You can use an EDIT function on your input variables to insert the special characters. Note that Francis has slashes in his date whereas you do not in your last 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
Expert
posted Hide Post
Ginny, I neglected to mention that you need slashes or dashes. Thanks for pointing that out.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Master
posted Hide Post
quote:

SET NODATA=' ',ASNAMES = ON
-*
-SET &BEG1 = '20090528';
-SET &ZBEG1 = EDIT(20090528,'9999/99/99');
-*
-SET &END1 = '20090528';
-SET &ZEND1 = EDIT(20090528,'9999/99/99');
-*
JOIN CLEAR *
JOIN PAT_ENC_CSN_ID IN HSCLARITY_ADT TO PAT_ENC_CSN_ID IN HSPAT_ENC_HSP
JOIN DEPARTMENT_ID IN HSCLARITY_ADT TO DEPARTMENT_ID IN HSCLARITY_DEP AS J1
JOIN BED_ID IN HSCLARITY_ADT TO BED_ID IN HSCLARITY_BED AS J2
-RUN
-*
TABLE FILE HSCLARITY_ADT
HEADING CENTER
" All Patients in PACU "
PRINT
PAT_ID
PAT_ENC_CSN_ID AS 'CSN,ID'
BILL_NUM
EVENT_ID AS 'EVENT'
HOSP_ADMSN_TIME AS 'ADMIT_TIME'
EFFECTIVE_TIME
DEPARTMENT_ID AS 'DEPT_ID'
COMPUTE XDEPT/A20 = GETTOK(DEPARTMENT_NAME,254,1,'-',20,XDEPT); AS 'UNIT'
XFER_EVENT_ID AS 'XFER_OUT'
XFER_IN_EVENT_ID AS 'XFER_IN'
EVENT_TYPE_C AS 'EVENT_TY'
EVENT_SUBTYPE_C AS 'EVENT_STY'
-*
BY PAT_CONTACT_MPI_NO AS 'MRN' SKIP-LINE
BY HOSP_ADMSN_TIME NOPRINT
BY EFFECTIVE_TIME NOPRINT
WHERE BILL_NUM EQ '10037058';
-*OR '10039594';
WHERE EVENT_TYPE_C EQ '1' OR '2' OR '3' OR '4';
WHERE EVENT_SUBTYPE_C NE '2';
WHERE EFFECTIVE_TIME GE DT(2009/05/28 00:00:00) AND EFFECTIVE_TIME LE DT(2009/05/28 23:59:59);
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE HOLD AS FX1
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5
-*


Here is the listing of fields in the FX1 Hold File
?FF FX1
-RUN

FILENAME= FX1
MRN E01 A25
PAT_ID E02 A18
CSN E03 P19
BILL_NUM E04 A25
EVENT E05 P19
ADMIT_TIME E06 HYYMDS
EFFECTIVE_TIME E07 HYYMDS
DEPT_ID E08 P19
DEPARTMENT_NAME E09 A254
UNIT E10 A20
XFER_OUT E11 P19
XFER_IN E12 P19
EVENT_TY E13 I11
EVENT_STY E14 I11

Once again, I get the same ERRORS...

quote:

DEFINE FILE FX1
IN_TIME/HYYMDS MISSING ON = IF (EVENT NE LAST EVENT) AND (EVENT EQ LAST XFER_IN) AND (EVENT_TY EQ '3')
THEN EFFECTIVE_TIME
ELSE MISSING;
-GOTO NOTZ
-NOTZ
END
-*
TABLE FILE FX1
HEADING CENTER
"TEST THE TIME STAMPS"
PRINT
EVENT
IN_TIME
-*OUT_TIME
EFFECTIVE_TIME
ADMIT_TIME
XFER_IN
XFER_OUT
EVENT_TY
BY UNIT
BY MRN
END
-RUN

0 ERROR AT OR NEAR LINE 61 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC36346) INCORRECT USE OF DATE-TIME FIELD OR CONSTANT
BYPASSING TO END OF COMMAND
0 ERROR AT OR NEAR LINE 77 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: IN_TIME
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
-EXIT


Yikes Confused


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
Have you tried it with dashes? Some data bases, like SQL Server, use dashes instead of slashes.


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
Master
posted Hide Post
Lemme try the dashes...

Q: Are you still railroading?

Thanks.


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Master
posted Hide Post
I tried it like:
quote:

-SET &BEG1 = '20090528';
-SET &ZBEG1 = EDIT(&BEG1,'9999-99-99');
-*
-SET &END1 = '20090528';
-SET &ZEND1 = EDIT(&END1,'9999-99-99');


I got the same error. Idea


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Virtuoso
posted Hide Post
I think it has something to do with the IF statement in the define.
When I code it like this:
CHECK/A1= IF (EVENT NE LAST EVENT) AND (EVENT EQ LAST XFER_IN) AND (EVENT_TY EQ 3) THEN 'E' ELSE 'M';
IN_TIME/HYYMDS MISSING ON = IF CHECK EQ 'E' THEN EFFECTIVE_TIME ELSE MISSING;

then I have no syntax error any more.

If you find the same behaviour, I'd open a case with IB if I were you. This defenitely seems to be a bug of some sort.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Master
posted Hide Post
Thanks GamP!


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Virtuoso
posted Hide Post
Glad to have been of help.
If the problem is solved - please add [solved] to the title of this post.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Master
posted Hide Post
Sorry all,

I have not figured out how to do that, or I would love to,

Tomsweb Cool


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
quote:
I have not figured out how to do that, or I would love to
It's all in the "Rules, Regulations, and FAQs" / "New Rule: Tag Your Topic with these Keywords" for this forum.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report 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     INCORRECT USE OF DATE-TIME FIELD - SOLVED!

Copyright © 1996-2020 Information Builders