Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
I'm using below script for incremental load from SQL DB , but it's not working at all. Please suggest what's wrong:
if filesize('$(vQVD)\CAQ_ABC.qvd') >0 then
trace >>>>>> $(vQVD)\CAQ_ABC.qvd ;
MAX_TIME:
load
max(DTTSERF) as LAST_LOAD_TIME
from $(vQVD)\CAQ_ABC.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
CAQ_ABC:
LOAD "Facility_Id",
Date,
Product,
Variant,
Station,
Serial,
Characteristic,
"Values",
Status,
DTTSERF;
SQL SELECT *
FROM MasterCAQ.dbo."CAQ_ABC"
WHERE DTTSERF > to_timestamp('$(vLAST_LOAD_TIME)', 'DD.MM.YYYY HH24:MI:SS')
;
// load prior values
if filesize('$(vQVD)\CAQ_ABC.qvd') >0 then
concatenate(CAQ_ABC)
load
*
from $(vQVD)\CAQ_ABC.qvd (qvd);
end if
// store all values
store CAQ_ABC into $(vQVD)\CAQ_ABC.qvd;
LET vCAQ_ABC_LOAD_DURATION = Interval(now() - vStart) ;
LET vStart = now();
LET vCAQ_ABC_Size = num(filesize('$(vQVD)\CAQ_ABC.qvd')/1024,'00.0') & ' KB';
LET vCAQ_ABC_Records = QvdNoOfRecords ('$(vQVD)\CAQ_ABC.qvd');
drop table CAQ_ABC;
Regards,
AS
Message was edited by: Amit Saini
Amit Saini wrote:
Hi Folks,
I'm using below script for incremental load from SQL DB , but it's not working at all. Please suggest what's wrong:
MAX_TIME:
load
max(DTTSERF) as LAST_LOAD_TIME
from $(vQVD)\CAQ_ABC.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
Is there any IF statement for this? If this is the full script you have Else won't work. Could be reason..
Sorry Anil,
Forgot below lines
if filesize('$(vQVD)\CAQ_ABC.qvd') >0 then
trace >>>>>> $(vQVD)\CAQ_ABC.qvd ;
and than it starts as per above comments.
But in general is there any way to do incremental load based on last reload time for below example:
CAQ_ABC:
LOAD "Facility_Id",
Date,
Product,
Variant,
Station,
Serial,
Characteristic,
"Values",
Status,
DTTSERF;
SQL SELECT *
FROM MasterCAQ.dbo."CAQ_ABC";
Thanks,
AS
May be this?
CAQ_ABC:
LOAD "Facility_Id",
Date,
Product,
Variant,
Station,
Serial,
Characteristic,
"Values",
Status,
DTTSERF;
SQL SELECT * FROM MasterCAQ.dbo."CAQ_ABC";
Store CAQ_ABC into [FilePath...];
Drop Table CAQ_ABC;
Sample:
Load * From <Your QVD>;
Last_Update_Reload:
Load Max(Date) as Max_Date Resident Sample;
Let VarForMax = Peek('Max_Date', 0, 'Last_Update_Reload');
Drop Table Sample;
In future, The Date may extend from current Max date to till next 2 Years. That means this?
First:
Load * From Sample_External_Source Where Date > $(VarForMax);
Concatenate
Load * From <Sample Qvd Path>;
Store First into ...;
Drop Table First;
Now, Load Fresh Table into QVD which is Last one
Load * From <First>;
Is that question resolved? If so, Try to flag