Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a filed "Updated TimedAte" in my qvd
I wanna do incremental update using this filed but i was not able to .
LOAD max(Timestamp((LAST_UPDATE_DATETIME)) as maxdate FROM 1.qvd (qvd);
LET vInc = 'WHERE Datetime(LAST_UPDATE_DATETIME) >=' & Peek('maxdate');
SQL Select * from Table where Last Updated Date Time >= $(vInc);
Mark,
vInc in its entirety is being substituted in to your SQL query, resulting in this:
Select * from Table where Last Updated Date Time >= WHERE Datetime(LAST_UPDATE_DATETIME) >=[MaxDate];
Change your vInc declaration to
LET vInc = chr(39) & Peek('maxdate') & chr(39);
LET vInc = 'WHERE Datetime(LAST_UPDATE_DATETIME) >=' & Peek('maxdate');
I feel this looks incorrect to me.
Hi Marcus,
I tried that, WHERE Datetime(LAST_UPDATE_DATETIME) >='10/28/2015 5:22:05 PM'
but getting the below error:
SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "DATETIME": invalid identifier
Mark,
just change your vInc declaration to this:
LET vInc = chr(39) & Peek('maxdate') & chr(39);
The error you got implies that you have DATETIME in your final query. If you had followed my recommendation that wouldn't be the case.
Marcus
Hi Marcus,
I followed:
LET vInc = chr(39) & Peek('maxdate') & chr(39);
FInal statement is:
FROM $(vDataSource) where LAST_UPDATE_DATETIME >= $(vInc);
ErrorMsg: [Oracle][ODBC][Ora]ORA-01843: not a valid month
The field is of timestamp,
Can you help me getting right format??
Ok, so what do you see in your QVW's log file?
One thing that might help is to amend the declaration to:
LET vInc = chr(39) & Date#(Peek('maxdate'), 'YYYY-MM-DD hh:mm:ss') & chr(39);
something like this
MaxDate:
LOAD
date(max($(vDateField)),'YYYY-MM-DD') as MaxDate
From $(vQvdPath)$(vTable).qvd(qvd);
Let vMaxDate = Peek('MaxDate',0,'MaxDate');
DROP Table MaxDate;
Also try this
Marcus,
When i try LET vInc = chr(39) & Date#(Peek('maxdate'), 'YYYY-MM-DD hh:mm:ss') & chr(39);
I see
"literal does not match format string"