topic Re: SQL Command not ended properly error in QlikView

SQL Command not ended properly error

markgraham123 — Thu, 29 Oct 2015 16:19:39 GMT

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

Re: SQL Command not ended properly error

marcus_malinow — Thu, 29 Oct 2015 16:30:02 GMT

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

Re: SQL Command not ended properly error

Anonymous — Thu, 29 Oct 2015 16:37:17 GMT

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

I feel this looks incorrect to me.

Re: SQL Command not ended properly error

markgraham123 — Thu, 29 Oct 2015 16:37:44 GMT

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

Re: SQL Command not ended properly error

marcus_malinow — Thu, 29 Oct 2015 16:40:01 GMT

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

Re: SQL Command not ended properly error

markgraham123 — Thu, 29 Oct 2015 16:45:08 GMT

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

Re: SQL Command not ended properly error

markgraham123 — Thu, 29 Oct 2015 16:45:57 GMT

The field is of timestamp,

Can you help me getting right format??

Re: SQL Command not ended properly error

marcus_malinow — Thu, 29 Oct 2015 16:51:24 GMT

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

Re: SQL Command not ended properly error

Anonymous — Thu, 29 Oct 2015 16:53:21 GMT

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

Re: SQL Command not ended properly error

markgraham123 — Thu, 29 Oct 2015 16:54:19 GMT

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"