[YDBOcto#382] Fix DATE conversion logic to apply to all DATE SQLTYPE values
Previous logic only did the conversion when FMFILE,FMFIELD,$P(^DD(FMFILE,FMFIELD,0),U,5)["%DT=" is TRUE
I COLUMNSQLTYPE="DATE",FMFILE,FMFIELD,$P(^DD(FMFILE,FMFIELD,0),U,5)["%DT=" D
This lead to some columns in table definition being a DATE type instead of being a FILEMAN specific DATE type.
Example:
9885 DROP TABLE IF EXISTS `HOSPITAL_LOCATION_PATTERN`;
9886 CREATE TABLE `HOSPITAL_LOCATION_PATTERN`(
9887 `HOSPITAL_LOCATION_ID` INTEGER PRIMARY KEY START 0 ENDPOINT '$CHAR(0)',
9888 `HOSPITAL_LOCATION_PATTERN_ID` INTEGER KEY NUM 1 START 0 ENDPOINT '$CHAR(0)',
9889 `PATTERN_DATE` DATE(FILEMAN) NOT NULL GLOBAL "^SC(keys(""hospital_location_id""),""ST"",keys(""hospital_location_pattern_id""),0)" PIECE 1,
9890 `CURRENT_AVAILABILITY` CHARACTER(90) EXTRACT "$E($G(^SC(keys(""hospital_location_id""),""ST"",keys(""hospital_location_pattern_id""),1)),1,90)",
9891 `MAINTENANCE` CHARACTER(8) EXTRACT "$$COMPEXP^%YDBOCTOVISTAM(44.005,2,keys(""hospital_location_id""),keys(""hospital_location_pattern_id""))",
9892 `SPECIAL_AVAILABILITY_FLAG` DATE GLOBAL "^SC(keys(""hospital_location_id""),""ST"",keys(""hospital_location_pattern_id""),9)" PIECE 1
9893 )
9894 GLOBAL "^SC(keys(""hospital_location_id""),""ST"",keys(""hospital_location_pattern_id""))" READONLY
9895 DELIM "^"
9896 AIMTYPE 1;
Because of such issues following errors were seen while running all tables having DATE columns in vista
select * from `PRESCRIPTION`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "2881109" is invalid for type "DATE", format "TEXT" and datestyle "ISO, YMD"
select * from `PRESCRIPTION_REFILL`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "2950509" is invalid for type "DATE", format "TEXT" and datestyle "ISO, YMD"
select * from `NON_VERIFIED_ORDERS`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "2931013.093647" is invalid for type "DATE", format "TEXT" and datestyle "ISO, YMD"
select * from `PHARMACY_PATIENT_IV`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "2971208.15" is invalid for type "DATE", format "TEXT" and datestyle "ISO, YMD"
select * from `PHARMACY_PATIENT_UNIT_DOSE`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "2890204.17" is invalid for type "DATE", format "TEXT" and datestyle "ISO, YMD"
select * from `LAB_DATA_CHEM_H_T_R_S_ETC`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "7059395.90931" is invalid for type "TIMESTAMP", format "FILEMAN" and datestyle "ISO, YMD"
select * from `LAB_DATA_MICROBIOLOGY`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "2911029" is invalid for type "DATE", format "TEXT" and datestyle "ISO, YMD"
select * from `CUM_MAJOR_H_M_H_C_D_TIME`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "7099579.8962" is invalid for type "TIMESTAMP", format "FILEMAN" and datestyle "ISO, YMD"
select * from `CUM_MISC_COLL_DATE_TIME`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "7099081.9" is invalid for type "TIMESTAMP", format "FILEMAN" and datestyle "ISO, YMD"
select * from `ACCESSION_DATE_ACC_NUMBER`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "7019186.887092" is invalid for type "TIMESTAMP", format "FILEMAN" and datestyle "ISO, YMD"
select * from `ADVERSE_REACTION_REPORTING`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "2921027.114" is invalid for type "DATE", format "TEXT" and datestyle "ISO, YMD"
select * from `SURGERY`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "2931217.21" is invalid for type "DATE", format "TEXT" and datestyle "ISO, YMD"
select * from `SURG_ANES_CARE_TIME_BLOCK`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "3061101.1003" is invalid for type "DATE", format "TEXT" and datestyle "ISO, YMD"
select * from `ONC_PRIM_SUBS_COUR_OF_TRE`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "2930122" is invalid for type "DATE", format "TEXT" and datestyle "ISO, YMD"
select * from `PRSE_EDUC_R_S_D_T_O_CLASS`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "2950427" is invalid for type "DATE", format "TEXT" and datestyle "ISO, YMD"
select * from `PROSTHETIC_SUSPENSE`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "3150706.154633" is invalid for type "DATE", format "TEXT" and datestyle "ISO, YMD"
select * from `ROR_HIST_DATA_EXTRACTION`;
[ERROR]: ERR_INVALID_DATE_TIME_VALUE: "3050401" is invalid for type "DATE", format "TEXT" and datestyle "ISO, YMD"
Since all values seen are FILEMAN specific the columns that are qualified to be DATE type needs to be converted to be a FILEMAN specific type.
Fix
- The IF block below did the conversion only when
FMFILE,FMFIELD,$P(^DD(FMFILE,FMFIELD,0),U,5)["%DT="
was also TRUE. This lead to some column not having FILEMAN specific type. This commit fixes this by ensuring the type is converted even whenCOLUMNSQLTYPE="DATE"
is the only part that is TRUE of the IF condition below.
; Convert Date column to Timestamp if the column could contain time
I COLUMNSQLTYPE="DATE",FMFILE,FMFIELD,$P(^DD(FMFILE,FMFIELD,0),U,5)["%DT=" D
. ; Input transform
. N IT S IT=$P(^DD(FMFILE,FMFIELD,0),U,5)
. ; Extract %DT string
. N QQ S QQ=$P($P(IT,"%DT=",2)," "),QQ=$P(QQ,",")
. ; Remove quotes
. N Q S Q=$E(QQ,2,$L(QQ)-1)
. I Q["T" S COLUMNSQLTYPE="TIMESTAMP(FILEMAN)"
. E S COLUMNSQLTYPE="DATE(FILEMAN)"
QUIT COLUMNSQLTYPE