7 Replies Latest reply: Jan 30, 2014 12:53 PM by Rebecca Donaldson RSS

    Sunquest Cache database incremental load


      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!

        • Re: Sunquest Cache database incremental load
          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 ?

            • Re: Re: Sunquest Cache database incremental load

              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:

               

              BatTstCodeReceiveDateReceiveDateODBCReceiveTimeReceiveTimeODBC
              CBC138811/01/2014001012:10:00 AM
              CBC138811/01/2014002812:28:00 AM
              CBC138811/01/2014004212:42:00 AM
              CBC138811/01/201407057:05:00 AM
              CBC138811/01/201414112:11:00 PM
              CBC138811/01/2014234111:41:00 PM
              CBC138821/02/2014000212:02:00 AM
              CBC138821/02/2014000312:03:00 AM
              CBC138821/02/2014001012: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]
              [C:\PROGRA~1\QlikView\QvConnect64.EXE]
              [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. 

            • Re: Sunquest Cache database incremental load
              Srikanth P

              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 .

                • Re: Sunquest Cache database incremental load

                  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!

                  • Re: Sunquest Cache database incremental load

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

                     

                    }