Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
markgraham123
Specialist
Specialist

SQL Command not ended properly error

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

9 Replies
marcus_malinow
Partner - Specialist III
Partner - Specialist III

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

Anonymous
Not applicable

LET vInc = 'WHERE Datetime(LAST_UPDATE_DATETIME) >=' & Peek('maxdate');


I feel this looks incorrect to me.


markgraham123
Specialist
Specialist
Author

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

marcus_malinow
Partner - Specialist III
Partner - Specialist III

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

markgraham123
Specialist
Specialist
Author

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

markgraham123
Specialist
Specialist
Author

The field is of timestamp,

Can you help me getting right format??

marcus_malinow
Partner - Specialist III
Partner - Specialist III

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

Anonymous
Not applicable

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

Incremental Load in Qlikview - Sources

markgraham123
Specialist
Specialist
Author

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"