Skip to content

[YDBOcto#382] Fix DATE conversion logic to apply to all DATE SQLTYPE values

Ganesh Mahesh requested to merge zylog1O1/YDBOctoVistA:ydboctovista382 into master

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 when COLUMNSQLTYPE="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

Merge request reports