Sunquest Cache database incremental load

[#1]

Experts, I'm actually  the developer, but am trying to help my architect deal with incremental loading for an app we are building over our Sunquest Cache db.  At present, a manual reload takes 2.5hr.

The date formats have been a challenge, but I've mostly conquered those in my load, but we have been unable to cut the amount of data down using a date limitation@.  I have an app date and also tried to force the date cut off in my where clause, but that also does not work.

We are looking for solutions on how to set up an incremental load.

I can forward any more db specific questions you need to clarify to help to my architect.

Thank you!

[whiteline]

Hi.

but we have been unable to cut the amount of data down using a date limitation

Why ? Maybe the date format is wrong and it's loaded as string instead of num ?

[#3]

Yes, the date formats in this database are very difficult to work with.  I have multi level loads for format the dates as useable.

Here are examples:

BatTstCode	ReceiveDate	ReceiveDateODBC	ReceiveTime	ReceiveTimeODBC
CBC	13881	1/01/2014	0010	12:10:00 AM
CBC	13881	1/01/2014	0028	12:28:00 AM
CBC	13881	1/01/2014	0042	12:42:00 AM
CBC	13881	1/01/2014	0705	7:05:00 AM
CBC	13881	1/01/2014	1411	2:11:00 PM
CBC	13881	1/01/2014	2341	11:41:00 PM
CBC	13882	1/02/2014	0002	12:02:00 AM
CBC	13882	1/02/2014	0003	12:03:00 AM
CBC	13882	1/02/2014	0010	12:10:00 AM

This is the set app date on the main:

  SET AppDate = '01/01/2011';

Where clause:

//and ReceiveDate >= '01/01/2011'
//where Date(ResultDate, 'MM/DD/YYYY') >= '$(AppDate)'

I have also tried using the ODBC dates, along with a lovely error message:

SQL##f - SqlState: S1000, ErrorCode: 146, ErrorMsg: [Cache ODBC][State : S1000][Native Code 146]

[SQLCODE: <-146>:<Unable to convert Date input to a valid Logical Date value>]
[Location: <ServerLoop - Query Open()>]
[%msg: <Error: '01/01/2011' is an invalid ODBC/JDBC Date value>]
SQL SELECT *,
case when (PriorityCodes = 'S' or AddOn = 'Y') then 'Y' else 'N'
end as PriorityAddOn

FROM SITE."U_GL_ACC_VIEW"
where (PriorityCodes = 'S' or AddOn='Y')
and ReceiveDateODBC >= '01/01/2011'

Load

*,
(CollectDTS - OrderDTS)* 1440 as OrdertoCollect,
(ReceiveDTS - CollectDTS)*1440 as CollectToReceive,
(ResultDTS - CollectDTS)*1440 as CollectToResult,
(ResultDTS - ReceiveDTS)*1440 as ReceiveToResults,
(ReceiveDTS - OrderDTS)*1440 as OrderToReceive,
(ResultDTS - OrderDTS)*1440 as OrderToResults,
If ((ReceiveDateODBC > ResultDateODBC),'Y','N') as Outlier

;

LOAD

CollListBatchNum,
DateLastActivity,
DateLastActivityODBC,
"GL_Accession_SpecData",
"LinkGL_Collect",
NumAccNumber,
RecDate,
// RecDateODBC,
RecTime,
// RecTimeODBC,
// Date(RecDateODBC, 'M/DD/YYYY') as RecDateODBC,
// Time(RecTimeODBC) as RecTimeODBC,
// num(RecDateODBC) + num(RecTimeODBC) as RecDTS,

So you can see we've been manipulating dates to even have usable data variables.

Current position - unable to automate the load cycle and the manual load takes 2.5+ hours against a production db.

Grateful for any pointers. 

[whiteline]

Hi.

There are two functions in QV Date and Date#.

The first one changes the text format of dual type.

The second one converts the text date in a proper format into dual (both text and numeric).

You should use Date#.

Then you can compare and subtract your dates.

[#5]

Hi Rebecca, If you don't have the datefield on the database, you need to convert the field into date value and then do date comparison for Incremental load.

Can you please post more about DB details, data types in the table, Primary key details so community will help .

[whiteline]

Hi.

It seems that you can use ReceiveDate instead of ReceiveDateODBC:

Where ReceiveDate > date#('01/01/2011', 'DD/MM/YYYY')-27759

[#7]

All,

Thank you for your feedback.  I've mastered date conversions in the load for my calculations.

Our current issues:

How to limit the load by date into the application  and how to program an incremental load going forward.

I've forwarded this link onto my architect to provide specific technical responses regarding the database structure.

Thank you!

[#8]

Hopefully this makes sense:

/// Accession Data
Class SITE.GLAccession Extends %Library.Persistent [ ClassType = persistent, Owner = _SYSTEM, Not ProcedureBlock, SqlRowIdName = GL_Accession, SqlTableName = GL_Accession, StorageStrategy = SQLStorage ]
{

Parameter EXTENTSIZE = 3250;

Index RowIDBasedIDKeyIndex On (AccessionDay, SequenceNumber) [ IdKey, PrimaryKey, Unique ];

Parameter READONLY = 1;

Parameter DEFAULTCONCURRENCY = 0;

/// Accession Sequence Number
Property SequenceNumber As %Library.Float(MAXVAL = 9999, MINVAL = 0) [ SqlColumnNumber = 3 ];

/// Accession Day (Single Alpha Character)
Property AccessionDay As %Library.String(COLLATION = "Exact", MAXLEN = 1, TRUNCATE = 0) [ SqlColumnNumber = 4 ];

/// Patient Internal Identifier
Property PIDX As %Library.String(COLLATION = "Exact", MAXLEN = 14, TRUNCATE = 0) [ SqlColumnNumber = 5 ];

/// Order Internal Identifier
Property ORDX As %Library.String(COLLATION = "Exact", MAXLEN = 14, TRUNCATE = 0) [ SqlColumnNumber = 6 ];

/// Pickup Required?
Property PickupFlag As SITE.MHMultChoice(DISPLAYLIST = ",Yes,No,No", MAXLEN = 3, SELECTIVITY = "25.0000%", VALUELIST = ",1,0,") [ SqlColumnNumber = 7 ];

/// Number of Unanswered Tests
Property NumUnanswered As %Library.Float [ SqlColumnNumber = 8 ];

/// Date of Last Activity (Julian)
Property LastActivityDJ As %Library.Float [ SqlColumnNumber = 9 ];

/// Last Activity Calendar Date
Property LastActivityDate As %Library.String(COLLATION = "Exact", MAXLEN = 18, TRUNCATE = 0) [ SqlColumnNumber = 10, SqlComputeCode = { S {LastActivityDate}="" Q:{LastActivityDJ}<1 S DJ={LastActivityDJ} D DJDT^DAT S {LastActivityDate}=DTT
}, SqlComputed, Transient ];

/// Collection List/Labels batch no.
Property CollectionList As %Library.String(COLLATION = "Exact", MAXLEN = 30, TRUNCATE = 0) [ SqlColumnNumber = 11 ];

/// Receive Date in Sunquest Julian format
Property ReceiveDJ As %Library.Float [ SqlColumnNumber = 12 ];

/// Receive Date
Property ReceiveDate As %Library.String(COLLATION = "Exact", MAXLEN = 15, TRUNCATE = 0) [ SqlColumnNumber = 13, SqlComputeCode = { S {ReceiveDate}="" Q:{ReceiveDJ}<1 s DJ={ReceiveDJ} d DJDT^DAT S {ReceiveDate}=DTT
}, SqlComputed, Transient ];

/// Receive Time
Property ReceiveTime As %Library.String(COLLATION = "Exact", MAXLEN = 8, TRUNCATE = 0) [ SqlColumnNumber = 14 ];

/// Preliminary Result Flag
Property PrelimResult As SITE.MHMultChoice(DISPLAYLIST = ",Yes,No,No", MAXLEN = 3, SELECTIVITY = "25.0000%", VALUELIST = ",1,0,") [ SqlColumnNumber = 15 ];

/// Microbiology Flag
Property MicroFlag As %Library.String(COLLATION = "Exact", MAXLEN = 10, TRUNCATE = 0) [ SqlColumnNumber = 16 ];

/// Rapid Order Flag
Property RapidOrderFlag As SITE.MHMultChoice(DISPLAYLIST = ",Yes,No,No", MAXLEN = 3, SELECTIVITY = "25.0000%", VALUELIST = ",1,0,") [ SqlColumnNumber = 17 ];

/// Complete Accession Number
Property AccessionNumber As %Library.String(COLLATION = "Exact", MAXLEN = 10, TRUNCATE = 0) [ SqlColumnNumber = 18, SqlComputeCode = { s {AccessionNumber}=""
s {AccessionNumber}={AccessionDay}_{SequenceNumber}
}, SqlComputed, Transient ];

/// Patient Name
Property PatientName As %Library.String(COLLATION = "Exact", MAXLEN = 25, TRUNCATE = 0) [ SqlColumnNumber = 19, SqlComputeCode = { S {PatientName}=""
q:{PIDX}="" n Name s Name=$p(^[LDIR,LMACH]LAB({PIDX}),"\",1) q:Name=""
s {PatientName}=$p(Name,"#",1)_", "_$p(Name,"#",2)_" "_$p(Name,"#",3) k Name
}, SqlComputed, Transient ];

/// Date of Last Activity, Date data type
Property LastActivityDateODBC As %Library.Date(FORMAT = 1) [ SqlColumnNumber = 20, SqlComputeCode = { i {LastActivityDJ}="" s {LastActivityDateODBC}="" q
s {LastActivityDateODBC}={LastActivityDJ}+49307
}, SqlComputed, Transient ];

/// Receive Date, Date data type
Property ReceiveDateODBC As %Library.Date(FORMAT = 1) [ SqlColumnNumber = 21, SqlComputeCode = { i {ReceiveDJ}="" s {ReceiveDateODBC}="" q
s {ReceiveDateODBC}={ReceiveDJ}+49307
}, SqlComputed, Transient ];

/// Receive Time, Time data type
Property ReceiveTimeODBC As %Library.Time [ SqlColumnNumber = 22, SqlComputeCode = { i {ReceiveTime}="" s {ReceiveTimeODBC}="" q
s {ReceiveTimeODBC}=$$T^LUD({ReceiveTime})
}, SqlComputed, Transient ];

/// Link to GL_Patient
Property LinkGLPatient As SITE.GLPatient [ SqlColumnNumber = 23, SqlComputeCode = { i {PIDX}="" s {LinkGL_Patient}="" q
S {LinkGL_Patient}={PIDX}
}, SqlComputed, SqlFieldName = LinkGL_Patient, Transient ];

/// Link to GL_Specimen
Property LinkGLSpecimen As SITE.GLSpecimen [ SqlColumnNumber = 24, SqlComputeCode = { i {PIDX}="" s {LinkGL_Specimen}="" q
i {ORDX}="" s {LinkGL_Specimen}="" q
s {LinkGL_Specimen}={PIDX}_"||"_{ORDX}
}, SqlComputed, SqlFieldName = LinkGL_Specimen, Transient ];

/// Link to GL_Collect
Property LinkGLCollect As SITE.GLCollect [ SqlColumnNumber = 25, SqlComputeCode = { s {LinkGL_Collect}={PIDX}_"||"_{ORDX}_"||"_{AccessionNumber}
}, SqlComputed, SqlFieldName = LinkGL_Collect, Transient ];

}