Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hy there ,
I`m trying to upload some info from a database wich is kinda big. So i have created a qvd file wich has the old information , and concatenate it with the new info. But every time i run the script it doubles the record values in my application, the qvd file size remains the same. What I`m doing wrong.
The script looks something like this:
LET lastReloadTime = ReloadTime();
IF QvdCreateTime('pathToQVD FILE') then
MyTable:
LOAD "DATE",
"COL1",
"COL2",
FROM [pathToQVD FILE] (qvd);
ELSE
MyTable:
LOAD "DATE",
"COL1",
"COL2";
SQL SELECT *
FROM "MY_TABLE" where trunc(DATE)>=to_date('20160101','YYYYMMDD') and trunc(DATE)<=to_date('20160115','YYYYMMDD');
END IF
Concatenate(MyTable)
LOAD
"COL1",
"COL2";
SQL SELECT *
FROM "MY_TABLE" where trunc(DATE)>=to_date('20160101','YYYYMMDD') and trunc(DATE)<=to_date('20160115','YYYYMMDD')
and 'DATE' > '$(lastReloadTime)';
store MyTable into [pathToQVD FILE];
Hi Razvan
Your code above is having issue in Where clause. I would suggest to use the following algorithm –
Please refer the example below –
let vFileExists = 0;
FOR EACH File IN ([pathToQVD FILE])
let vFileExists = 1;
NEXT File
IF $(vFileExists) = 1 then
Last_Load_Date:
LOAD
MaxString(DATE) as MaxDate
FROM [pathToQVD FILE](qvd);
let vMaxDate= peek('MaxDate',0,'Last_Load_Date');
MyTable:
Load
DATE
, COL1
, COL2;
SELECT DATE, COL1, COL2 from "MY_TABLE" where trunc(DATE)>'$(vMaxDate)';
CONCATENATE
Load
DATE
, COL1
, COL2;
FROM [pathToQVD FILE](qvd);
ELSE
MyTable:
Load
DATE
, COL1
, COL2;
SELECT DATE, COL1, COL2 from "MY_TABLE" where trunc(DATE)>'$(vMaxDate)';
END IF
store MyTable into [pathToQVD FILE];
DROP Table MyTable ;
Hy Vinod ,
Thank a lot for your reply. In mean time I`ve made it with the script I wrote , the problem was in my where clause.
But now I want to do something else. Because the data is massive , and my qvd is bigger than 1 GB , I would like to save my data into qvd by periods. When I say period , I would like to make a new qvd file every 3 months. For example in my DATA field I have info that starts from 01-JAN-2016 and ends ,let`s say today. So I would like to create a qvd file something like this:
if(DATA>=01-JAN-2016 & DATA <01-APR-2016) store in FILE_JAN_MAR.qvd
else
if(DATA>=01-APR-2016 & DATA <01-JUL-2016)store in FILE_APR_JUL.qvd
and so on.
And the reloading of this qvd files should be done for all of them.
How can I achive this type of saving and reloading?
And I have one more problem. Because there is a lot of information , my app is a bit slower. How can I make it faster.
Thank in advance.