3 Replies Latest reply: Sep 7, 2017 11:06 AM by Viresh Kolagimath RSS

    issues with incremental load

    Viresh Kolagimath

      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

        • Re: issues with incremental load
          Sunny Talwar

          What is the exact script you are using?

            • Re: issues with incremental load
              Viresh Kolagimath

              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.