Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
markgraham123
Contributor II

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
Valued Contributor III

Re: SQL Command not ended properly error

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

ink12345
Contributor

Re: SQL Command not ended properly error

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


I feel this looks incorrect to me.


markgraham123
Contributor II

Re: SQL Command not ended properly error

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
Valued Contributor III

Re: SQL Command not ended properly error

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
Contributor II

Re: SQL Command not ended properly error

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
Contributor II

Re: SQL Command not ended properly error

The field is of timestamp,

Can you help me getting right format??

marcus_malinow
Valued Contributor III

Re: SQL Command not ended properly error

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

ink12345
Contributor

Re: SQL Command not ended properly error

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
Contributor II

Re: SQL Command not ended properly error

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"

Community Browser