Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vireshkolagimat
Creator III
Creator III

ORA-01861: literal does not match format string

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

18 Replies
vireshkolagimat
Creator III
Creator III
Author

Hi, but for normal load it works fine without any issues. only for incremental load i am facing the issue.

marcus_malinow
Partner - Specialist III
Partner - Specialist III

Ok, so you're mixing your date formats, as in you have the WHERE clause

Where TRANSACTIONDATE > '01-Jan-14'

Also, it looks like you're missing a SELECT, and have a superfluous comma in your SQL block, which should be like so

SQL

SELECT

  TRANSACTIONDATE,

FROM APPS.XXEGC_QV_SALES_PIPELINE_V

Where TRANSACTIONDATE > '01-Jan-14'

vireshkolagimat
Creator III
Creator III
Author

Hi script looks fine. The only issue i think is the variable i have created. Not able to figure out what's wrong.

marcus_malinow
Partner - Specialist III
Partner - Specialist III

Er I beg to differ. Script is most definitely wrong. Hence you are getting errors. If your script is fine, why are you posting issues?

Another issue,

prior to the SQL keyword, you have a comma, and no semi colon.

$(vTableName):

  LOAD

  floor(TRANSACTIONDATE) as TransactionDate,

;

SQL

SELECT

  TRANSACTIONDATE,

FROM APPS.XXEGC_QV_SALES_PIPELINE_V

Where TRANSACTIONDATE > '01-Jan-14'

vireshkolagimat
Creator III
Creator III
Author

Hi,

That was  by mistake i added while pasting the code. I have semicolon in my script.

I am able to run the script without any issue for normal load but for incremental load i am getting the error.

thanks for the suggestion.

Regards,

Viresh

marcus_malinow
Partner - Specialist III
Partner - Specialist III

Ok - check this

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

What is in vReloadTime?

Perhaps you could try changing it to:

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

vireshkolagimat
Creator III
Creator III
Author

Hi All,

I got the solution. Actually it is issue with formatting the date. In the where clause it was reading the date as YYYY-MM-DD instead DD-MMM-YY. SO i changed the date format for vLast30day and vReloadTime and finally it is working fine.

here is the update variable expression.

LET vIncrementalExpression = 'Where CUSTOMERCODE not in (''A'',''B'',''C'') and InvoiceDate >=' & Chr(39)& '$(vLast30date)' &Chr(39) &

' and InvoiceDate <=' &Chr(39)& date(vReloadTime,'DD-MMM-YY') &Chr(39);

thank you all for your valuable suggestions and tips.

Regards,

Viresh

Anil_Babu_Samineni

Good, That is where i declare check in Date format because 01861 is the Format issue in Oracle.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Emmanuelle__Bustos
Partner - Specialist
Partner - Specialist

We had the same issue using Oracle OLEDB driver and a sentence:

SQL Select

A,

B,

C

from...

where (vDate...)  

and the solution was automatically when switching to a Oracle ODBC driver without need to changing nothing in script.