17 Replies Latest reply: Aug 31, 2017 10:08 AM by Anil Babu Samineni RSS

    ORA-01861: literal does not match format string

    Viresh Kolagimath

      Hi All,

       

      I am getting error literal does not match format string while executing the below script. I am getting the data from oracle database.

       

      If QVD not exists, it will run without any issue. but for incremental loading  i am facing the above issue.

       

      I am using vIncrementalExpression  variable to filter for latest records.

       

      SET vQvdFile='C:\SalesPipeling.qvd';

      SET vTableName='SalesPipeline';

      SET vPK='SalesSalesPipelineId';

       

      LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);

       

      IF $(vQvdExists) THEN

      maxdateTab:

      LOAD max(TransactionDate) as maxdate

      FROM $(vQvdFile) (qvd);

       

      LET vMaxdate = Date(Peek('maxdate',0,'maxdateTab'),'YYYY-MM-DD');

      LET vLast30Days = Date(Peek('maxdate',0,'maxdateTab')-30,'YYYY-MM-DD');

       

      IF IsNull(vMaxdate) then

      LET vIncrementalExpression = '';

      ELSE

      LET vIncrementalExpression = 'and TransactionDate >= '&Chr(39)& '$(vLast30Days)' &Chr(39)&

      ' and TransactionDate <=' &Chr(39)& date(vReloadTime,'YYYY-MM-DD')&Chr(39);

      ENDIF;

      DROP table maxdateTab;

       

      ELSE // QVD does not exist

      LET vIncrementalExpression = 'and extract( YEAR from TransactionDate) >= extract(YEAR from sysdate)-3';

       

       

      END IF

       

       

      $(vTableName):

        LOAD

        floor(TRANSACTIONDATE) as TransactionDate,

       

      SQL

        TRANSACTIONDATE,

       

      FROM APPS.XXEGC_QV_SALES_PIPELINE_V

      Where TRANSACTIONDATE > '01-Jan-14'

      $(vIncrementalExpression) ;     // Include WHERE clause created in "Incremental Setup" tab

       

      IF $(vQvdExists) THEN

      // Use CONCATENATE in case we've added any new fields.

      CONCATENATE ($(vTableName)) LOAD Distinct *  FROM $(vQvdFile) (qvd)

      WHERE TransactionDate < '$(vLast30Days)';

       

      END IF;

       

      If ScriptErrorCount = 0 then

      Call StoreAndDrop(vTableName,vQvdFile);

      ENDIF;

       

      SET vQvdFile='';

      SET vTableName='';

      SET vPK='';

       

       

      Regards,

      Viresh