50 Replies Latest reply: Apr 12, 2016 5:48 AM by Amit Saini RSS

    Script help

    Amit Saini

      Hi Folks,

       

      I'm having one table called "STICHPROBE" , so normally we are running script below for QVD generator to load data every 2 hours from Database.

       

      Suppose script started running every Monday than Tuesday...Wed...Thus... But on Friday What I'm looking is to load data once again from Monday . So on every Friday's of the Month we gonna load data once again from Monday.

       

      So could u please help how this can be handled by using below script:

       

      ********************************************Script Start***********************************************************

      LET vStartDate = date(makedate(2015,1), 'DD.MM.YYYY hh:mm:ss');

       

      Set vQVD = 'D:\qvprod\QV_QVD';

      Let vQVDCurrentYear = 'D:\qvprod\QV_QVD\'& year(today());

      $(Include=.....\0_db_connections.qvs)

       

      Let vStart = now();

      Let vReloadStart = now();

       

      ///$tab STICHPROBE

      if filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd') >0  then

       

        trace >>>>>> $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd    ;

       

        MAX_TIME:

        load

           max(DTTSERF) as LAST_LOAD_TIME

        from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd);

       

        let vLAST_LOAD_TIME =  timestamp(peek('LAST_LOAD_TIME',0,'MAX_TIME'), 'DD.MM.YYYY hh:mm:ss');

       

        trace >>>>>> LAST_LOAD_TIME:  $(vLAST_LOAD_TIME);

       

        drop table MAX_TIME;

       

      else

       

        LET vLAST_LOAD_TIME = '$(vStartDate)';

        trace >>>>>> LAST_LOAD_TIME:  $(vLAST_LOAD_TIME);

       

      end if

       

      // load new values

      STICHPROBE:

      LOAD

          '$(vPlant)' as Plant,

          *;

      SQL SELECT

      *

      FROM $(vPlantAutor).STICHPROBE

      WHERE DTTSERF > to_timestamp('$(vLAST_LOAD_TIME)', 'DD.MM.YYYY HH24:MI:SS');

       

      // load prior values

      if filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd') >0  then

       

      concatenate(STICHPROBE)

      load

      *

      from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd);

       

      end if

       

      // store all values

      store STICHPROBE into $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd;

       

      LET vSTICHPROBE_LOAD_DURATION = Interval(now() - vStart) ;

      LET vStart = now();

       

      LET vSTICHPROBE_Size = num(filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd')/1024,'00.0') & ' KB';

      LET vSTICHPROBE_Records = QvdNoOfRecords ('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd');

       

       

       

      *************************************************************************Script End******************************************************************

       

      Thanks,

      AS