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
I hope I have corrected now , but still same issue.
Thanks
AS
I see what you are doing. One more try at this now
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 or Num(WeekDay($(vLAST_LOAD_TIME))) = 0, WeekStart($(vLAST_LOAD_TIME)), $(vLAST_LOAD_TIME)));
Now this is Oracle Error and I have limited expertise in this, but what value would you enter when you run this query in Oracle (What would you replace '42471.672164352') to get an output?
Sorry Sunny no idea
Who created this SQL query for you? Can you ask what format is the SQL expecting to see? I would as if this is right or not -> WHERE DTTSERF > to_timestamp('42471.672164352', 'DD.MM.YYYY HH24:MI:SS');
I need to check this ,will come back to u.
Thank u so much
Thanks
AS
Yup, keep us posted
Hi
suggestion -
for this requirement, in sql where clause you can use DECODE statement directly.
No long script required as i guess.
Regards,
Lokesh
Hi Sunny ,
Below is info for this error :
(From SQL support team):
I think it is the number value in the function to_timestamp. Usually Oracle-DB requested a string like 12.04.2016.
Here you find more information:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions193.htm
Thanks,AS