Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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');
Already tried this format , but still same error.
Thanks,
AS
When you trace vLAST_LOAD_TIME do you see number or date?
Like this???
Thanks.
AS
Oh I though we were passed this stage of the error. Are we still stuck on vLoadFrom? Didn't we reach the Oracle error?
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
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);
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?
Hi Kushal,
Yes this is working with any error , but need to check if result is ok or not.
Thank you!
AS
I mean working without error!