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

[sunny_talwar]

Do you mean this format?

Try this:

SET TimestampFormat='MM.DD.YYYY hh:mm:ss';

MAX_TIME:

LOAD Max(DTTSERF) as LAST_LOAD_TIME

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

LET vLAST_LOAD_TIME =  Peek('LAST_LOAD_TIME');

LET vLoadFrom = TimeStamp(If(Num(WeekDay($(vLAST_LOAD_TIME))) >= 6 orNum(WeekDay($(vLAST_LOAD_TIME))) = 0, WeekStart($(vLAST_LOAD_TIME)), $(vLAST_LOAD_TIME)), 'DD-MMM-YY hh:mm:ss');

[amit_saini]

Already tried this format , but still same error.

Thanks,
AS

[sunny_talwar]

When you trace vLAST_LOAD_TIME do you see number or date?

[amit_saini]

Like this???

Thanks.

AS

[sunny_talwar]

Oh I though we were passed this stage of the error. Are we still stuck on vLoadFrom? Didn't we reach the Oracle error?

Re: Script help

[amit_saini]

Actually if I;m using this:

SET TimestampFormat='MM.DD.YYYY hh:mm:ss';

MAX_TIME:

LOAD Max(DTTSERF) as LAST_LOAD_TIME

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

LET vLAST_LOAD_TIME =  Peek('LAST_LOAD_TIME');

LET vLoadFrom = TimeStamp(If(Num(WeekDay($(vLAST_LOAD_TIME))) >= 6 orNum(WeekDay($(vLAST_LOAD_TIME))) = 0, WeekStart($(vLAST_LOAD_TIME)), $(vLAST_LOAD_TIME)), 'DD-MMM-YY hh:mm:ss');

Than we are having script line error before oracle one.

attached is log file.

Thanks,

AS

[Kushal_Chawda]

have you tried this? Just need to change this part rest all script will remain the same.

MAX_TIME:

  load if(weekday(LAST_LOAD_TIME1)>=4 and weekday(LAST_LOAD_TIME1)<=6, weekstart(LAST_LOAD_TIME1),LAST_LOAD_TIME1) as LAST_LOAD_TIME;

  load

     max(DTTSERF) as LAST_LOAD_TIME1

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

[sunny_talwar]

Amit, I don't see what the issue might be except this.

Can you add a space between or and Num and see if that do anything?

[amit_saini]

Hi Kushal,

Yes this is working with any error , but need to check if result is ok or not.

Thank you!

AS

[amit_saini]

I mean working without error!