topic Re: ORA-01861: literal does not match format string in QlikView

ORA-01861: literal does not match format string

vireshkolagimat — Wed, 30 Aug 2017 09:44:42 GMT

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

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

Anil_Babu_Samineni — Wed, 30 Aug 2017 10:05:31 GMT

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

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

kenphamvn — Wed, 30 Aug 2017 10:08:38 GMT

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

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

vireshkolagimat — Wed, 30 Aug 2017 10:14:39 GMT

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

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

vireshkolagimat — Wed, 30 Aug 2017 10:15:31 GMT

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

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

kenphamvn — Wed, 30 Aug 2017 10:26:06 GMT

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) ;

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

marcus_malinow — Wed, 30 Aug 2017 10:26:53 GMT

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

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

vireshkolagimat — Wed, 30 Aug 2017 10:28:14 GMT

vLast30Days=2017-7-31

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

Anil_Babu_Samineni — Wed, 30 Aug 2017 10:28:26 GMT

Could be issue here?

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

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

Or — Wed, 30 Aug 2017 10:29:42 GMT

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.

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

vireshkolagimat — Wed, 30 Aug 2017 10:35:36 GMT

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

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

marcus_malinow — Wed, 30 Aug 2017 10:41:47 GMT

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'

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

vireshkolagimat — Wed, 30 Aug 2017 10:45:44 GMT

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

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

marcus_malinow — Wed, 30 Aug 2017 10:51:29 GMT

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'

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

vireshkolagimat — Wed, 30 Aug 2017 11:05:02 GMT

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

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

marcus_malinow — Wed, 30 Aug 2017 11:13:02 GMT

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);

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

vireshkolagimat — Thu, 31 Aug 2017 13:55:35 GMT

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

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

Anil_Babu_Samineni — Thu, 31 Aug 2017 14:08:30 GMT

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

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

Emmanuelle__Bustos — Fri, 04 Oct 2019 17:24:52 GMT

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

SQL Select

from...

where (vDate...)

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