INCORRECT USE OF DATE-TIME FIELD

August 31, 2009, 11:39 AM

Tomsweb

INCORRECT USE OF DATE-TIME FIELD - SOLVED!

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?

Thanks!

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

August 31, 2009, 11:56 AM

GinnyJakes

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.

August 31, 2009, 12:01 PM

Tomsweb

-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);

August 31, 2009, 12:48 PM

Francis Mariani

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.

August 31, 2009, 12:58 PM

GinnyJakes

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.

August 31, 2009, 01:03 PM

Francis Mariani

Ginny, I neglected to mention that you need slashes or dashes. Thanks for pointing that out.

August 31, 2009, 04:32 PM

Tomsweb

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

August 31, 2009, 04:42 PM

GinnyJakes

Have you tried it with dashes? Some data bases, like SQL Server, use dashes instead of slashes.

August 31, 2009, 04:49 PM

Tomsweb

Lemme try the dashes...

Q: Are you still railroading?

Thanks.

August 31, 2009, 04:52 PM

Tomsweb

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.

September 01, 2009, 03:51 AM

GamP

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.

September 02, 2009, 11:37 AM

Tomsweb

Thanks GamP!

September 02, 2009, 03:55 PM

GamP

Glad to have been of help.
If the problem is solved - please add [solved] to the title of this post.

September 02, 2009, 09:19 PM

Tomsweb

Sorry all,

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

Tomsweb

September 02, 2009, 10:50 PM

Doug

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.

- Using AS 8.2.01 on Windows 10 - IE11.
	in Focus since 1988

- Using AS 8.2.01 on Windows 10 - IE11.
	in Focus since 1988