Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Incremental Load Fail - SQL command not properly ended

New to incremental loading and getting hung up on the getting data after last update.  It feels like a solution that is easy but I cannot figure it out.  Hoping the Qlik community can help!

This is an Oracle DB I'm querying to

Load of UPDATEDATE field only from my QVD.  Others not needed for comparison


//CMRSALES
CMRSALES:
LOAD UPDATEDATE
FROM

(
qvd);

Create LastUpdate field in DATE format of the DB. 

//LastUpdate
LastUpdateTime:
LOAD DATE(MAX(UPDATEDATE),'MM/DD/YYYY hh:mm:ss TT') as LastUpdate
resident CMRSALES;

Set LastUpdateVar Variable to use for comparison

//LastUpdateVariable
Let LastUpdateVar = peek('LastUpdate',0,'LastUpdateTime');


Comparison Load to get latest data from DB

LOAD *;
SQL SELECT *
FROM GFXR.CMRSALESPNL
WHERE UPDATEDATE>
$(LastUpdateVar);

Error Message.

I can see LastUpdateVar is correct.  I've tried different variations of using UPDATEDATE as NUM(UPDATEDATE) or formatting

ErrorSource: OraOLEDB, ErrorMsg: ORA-00933: SQL command not properly ended

SQL SELECT *

FROM GFXR.CMRSALESPNL

WHERE UPDATEDATE>10/07/2015 11:59:19 PM

3 Replies
fernando_tonial
Valued Contributor

Re: Incremental Load Fail - SQL command not properly ended

Hi, Try this.

SQL SELECT *
FROM GFXR.CMRSALESPNL
WHERE UPDATEDATE>'
$(LastUpdateVar)';

Not applicable

Re: Incremental Load Fail - SQL command not properly ended

Thank you Fernando.  It feels like I am getting closer but still seeing this error.

ErrorSource: OraOLEDB, ErrorMsg: ORA-01843: not a valid month

SQL SELECT *

FROM GFXR.CMRSALESPNL

WHERE UPDATEDATE >= '10/07/2015 11:59:19 PM'

I tried using both but neither solved the valid month issue

LOAD DATE(MAX(UPDATEDATE),'MM/DD/YYYY hh:mm:ss TT') as LastUpdate

and

LOAD DATE(MAX(UPDATEDATE),'DD/MM/YYYY hh:mm:ss TT') as LastUpdate

fernando_tonial
Valued Contributor

Re: Incremental Load Fail - SQL command not properly ended

Bryan,

You should check the format of the field in oracle.

Oracle Timestamp literal format is 'YYYY-MM-DD hh:mm:ss TT'

You can try this.

LOAD MAX(UPDATEDATE) as LastUpdate

or

LOAD DATE(MAX(UPDATEDATE),'YYYY-MM-DD hh:mm:ss TT') as LastUpdate

Best Regards.

Tonial.

Community Browser