Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
vireshkolagimat
Contributor II

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

1 Solution

Accepted Solutions
vireshkolagimat
Contributor II

Re: ORA-01861: literal does not match format string

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

17 Replies

Re: ORA-01861: literal does not match format string

May be check this in Debug option rather full download and highlight that line to troubleshoot. That may help to get into work

Life is so rich, and we need to respect to the life !!!
kenphamvn
Contributor III

Re: ORA-01861: literal does not match format string

I think there are some mistakes in SQL statement ($(vIncrementalExpression))

Please run in debug mode or check log script, you can see more detail about SQL statement

vireshkolagimat
Contributor II

Re: ORA-01861: literal does not match format string

Hi, This is what i see in the debugger.

ScriptErrorDetails "SQL##f - SqlState: S1000, ErrorCode: 1861, ErrorMsg: [Oracle][ODBC][Ora]ORA-01861: literal does not match format string "

ScriptErrorList General Error 

ScriptError General Error

vireshkolagimat
Contributor II

Re: ORA-01861: literal does not match format string

I am not able to find any error in the log file.

kenphamvn
Contributor III

Re: ORA-01861: literal does not match format string

Hi

you can using TRACE for output SQL statement ,copy result and run in Oracle Environment

like this

TRACE SQL TRANSACTIONDATE, FROM APPS.XXEGC_QV_SALES_PIPELINE_V Where TRANSACTIONDATE > '01-Jan-14'$(vIncrementalExpression) ; 

marcus_malinow
Valued Contributor III

Re: ORA-01861: literal does not match format string

I'd suggest looking at your vLast30Days variable. Is this being set up correctly? What value does it contain following the LET statement?

vireshkolagimat
Contributor II

Re: ORA-01861: literal does not match format string

vLast30Days=2017-7-31

Re: ORA-01861: literal does not match format string

Could be issue here?

Date(TRANSACTIONDATE,'Your Format') as TransactionDate;

Life is so rich, and we need to respect to the life !!!
Or
Valued Contributor II

Re: ORA-01861: literal does not match format string

That particular Oracle SQL message usually indicates you've attempted to format something as a date that Oracle can't format as a date. Double-check that the formatted dates you are sending the SQL via variables are formatted in the way Oracle expects them, either by replacing the dynamic date ranges with manual date inputs in the same format, or by loading the dynamic values into your regular script and checking the format.

Community Browser