Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have QVD file which was generated by SQL server database.
Now i am generating the another QVD file from oracle database (the table in oracle has the same structure like the table in SQL server) and trying to concatenate with the existing QVD file which was generated by the SQL server database.
But after incremental load, its over writing the existing QVD file and keeping only the data which is from oracle database.
Kindly suggest me how to to fix it.
Regards,
Viresh
What is the exact script you are using?
Hi, here is the code:
/* Setup for incremental load. */
SET vQvdFile='C:\Users\VireshK\Desktop\Migration\AB\Data\QVD\Raw_Qvds\ConsSalesHistory.qvd'; //?? The QVD filename
SET vTableName='SalesHistory'; //?? The name of the QV table we are loading on the Data Load tab
SET vPK='SalesHistoryId'; //?? Data Primary Key
// Set a variable indicating if the QVD exists or not. -1 is True, 0 is False
LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);
IF $(vQvdExists) THEN // QVD exists, we will do an incremental reload
maxdateTab:
// Get the max date from this QVD for use in incremental SELECT
LOAD max(InvoiceDate) as maxdate //?? Set the name of the date or datetime field
FROM $(vQvdFile) (qvd);
LET vMaxdate = Date(Peek('maxdate',0,'maxdateTab'),'YYYY-MM-DD');
LET vLast30date = Date(Peek('maxdate',0,'maxdateTab')-30,'DD-MMM-YY');
IF IsNull(vMaxdate) then
LET vIncrementalExpression = '';
ELSE
// LET vIncrementalExpression = 'Where YEAR (salhist.dated) >= Year (GETDATE ()) - 3 and Convert(Varchar(8),salhist.dated, 112) >=' & Chr(39)& $(vLast30date) &Chr(39) &
LET vIncrementalExpression = 'and CUSTOMERCODE not in (''L'',''M'',''N'') and InvoiceDate >=' & Chr(39)& '$(vLast30date)' &Chr(39) &
' and InvoiceDate <=' &Chr(39)& date(vReloadTime,'DD-MMM-YY') &Chr(39);
ENDIF;
DROP table maxdateTab;
ELSE // QVD does not exist
//LET vIncrementalExpression = 'Where CUSTOMERCODE not in (''CCRE001'',''CCRE002'',''CNRE001'') and YEAR(INVOICEDATE) >= (Year (sysdate()) - 3)';
LET vIncrementalExpression = 'and CUSTOMERCODE not in (''L'',''M'',''N'') and extract( YEAR from InvoiceDate) >= (extract (YEAR from sysdate)-3)'; // No QVD. Force full reload
END IF
/* End of Setup for incremental load. */
/*
Load the incremental changes or full reload.
This is a standard LOAD (or SQL SELECT) statement.
*/
$(vTableName):
LOAD *;
SQL SELECT DIVISION_CODE,
DIVISION_NAME,
CUSTOMERCODE,
WAREHOUSECODE,
PRODUCTCODE,
INVOICENO,
INVOICEDATE,
SALESCOST,
SALESLISTVALUE,
SALESCOMMISSION,
SALESCUSTOMERGROUP,
SALESTERRITORY,
REGIONCODE,
CHANNELCODE,
SALESMANCODE,
ROUTECODE,
SALESUNIT,
SALESUNITFACTOR,
RETURNTYPE,
SALESQTY,
SALESVALUE
FROM APPS.XXEGC_QV_SALES_HISTORY_V
where DIVISION_CODE='A'
// If doing a SQL SELECT, the vIncrementalExpression would be part of the SQL SELECT statement.
$(vIncrementalExpression); // Include WHERE clause created in "Incremental Setup" tab
/*End of Load*/
/*
Update the QVD with changes.
*/
Directory C:\Users\VireshK\Desktop\Migration\AB\Data\QVD\Raw_Qvds;
// If incremental reload was , load previous data and concatenate to data just read.
IF $(vQvdExists) THEN
// Use CONCATENATE in case we've added any new fields.
CONCATENATE ($(vTableName))
LOAD Distinct * FROM $(vQvdFile) (qvd)
WHERE InvoiceDate < '$(vLast30date)'; // Load only QVD rows that were not already loaded in the data load.
END IF;
/*
Overwrite the QVD with the QV datatable.
*/
If ScriptErrorCount = 0 then
Call StoreAndDrop(vTableName,vQvdFile);
ENDIF;
SET vQvdFile='';
SET vTableName='';
SET vPK='';
exit Script
thank you.
Hi Sunny,
Could you please let me know if you are able to figure out the issue.
thank you.
Regards,
Viresh