topic Re: Incremental Load Fail - SQL command not properly ended in QlikView

Incremental Load Fail - SQL command not properly ended

Fri, 09 Oct 2015 19:34:52 GMT

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

Re: Incremental Load Fail - SQL command not properly ended

fernando_tonial — Fri, 09 Oct 2015 20:46:14 GMT

Hi, Try this.

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

Re: Incremental Load Fail - SQL command not properly ended

Fri, 09 Oct 2015 21:07:17 GMT

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

Re: Incremental Load Fail - SQL command not properly ended

fernando_tonial — Wed, 14 Oct 2015 14:04:11 GMT

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

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

Best Regards.

Tonial.