Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type. Showing results for Search instead for Did you mean:
2017-08-30 05:44 AM
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
2017-08-31 09:55 AM 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 18 Replies
2017-08-30 06:05 AM
May be check this in Debug option rather full download and highlight that line to troubleshoot. That may help to get into work Please add me @Anil_Babu_Samineni to interact faster when reply back. 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)
2017-08-30 06:14 AM Author
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
2017-08-30 06:26 AM
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) ;
2017-08-30 06:28 AM
Could be issue here? Date(TRANSACTIONDATE,'Your Format') as TransactionDate; Please add me @Anil_Babu_Samineni to interact faster when reply back. 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)  2017-08-30 06:29 AM
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. |
