Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi, but for normal load it works fine without any issues. only for incremental load i am facing the issue.
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'
Hi script looks fine. The only issue i think is the variable i have created. Not able to figure out what's wrong.
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'
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
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);
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
Good, That is where i declare check in Date format because 01861 is the Format issue in Oracle.
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.