topic Year command reading Oracle using Qlik ODBC Connector Package in App Development

Year command reading Oracle using Qlik ODBC Connector Package

alexis — Sat, 23 Jan 2016 08:25:28 GMT

Just installed Qlik ODBC Connector Package and seems to work great.

I wanted to load a large transactional table and limit it for all records since 1/1/2014.

I started with:

[Sales]:

LOAD ITEM_CODE,

M_SALESDATE,

M_CARD_SEQ_NO,

........

;

SELECT "ITEM_CODE",

"M_SALESDATE",

"M_CARD_SEQ_NO",

......

FROM "SCORC"."TMP_ALL_SALES"

WHERE YEAR("M_SALESDATE") > 2013

;

and this produced the error:

ERROR [42S22] [Qlik][ODBC Oracle Wire Protocol driver][Oracle]ORA-00904: "YEAR": invalid identifier

"M_SALESDATE" is a datetime field

I also tried with and without quotes for the field name.

Next I tried the Oracle specific:

WHERE EXTRACT (year FROM M_SALESDATE) > 2013

and

WHERE EXTRACT (year FROM DATE "M_SALESDATE") > 2013

and got

ERROR [HY000] [Qlik][ODBC Oracle Wire Protocol driver][Oracle]ORA-00936: missing expression

So now I am running out of options - can someone help.

Thanks in advance

Alexis

Re: Year command reading Oracle using Qlik ODBC Connector Package

Gysbert_Wassenaar — Sat, 23 Jan 2016 10:55:37 GMT

WHERE EXTRACT (year FROM DATE "M_SALESDATE") > 2013

That's looks correct to me. But if that refuses to work then you can try using a date instead:

WHERE M_SALESDATE >= TO_DATE('2014-01-01','YYYY-MM-DD').

If necessary you can also put the where clause in the preceding load:

[Sales]:

LOAD ITEM_CODE,

M_SALESDATE,

M_CARD_SEQ_NO,

........

WHERE YEAR("M_SALESDATE") > 2013

;

SELECT "ITEM_CODE",

"M_SALESDATE",

"M_CARD_SEQ_NO",

......

FROM "SCORC"."TMP_ALL_SALES"

Re: Year command reading Oracle using Qlik ODBC Connector Package

alexis — Mon, 25 Jan 2016 15:36:07 GMT

Hi Gysbert

Thanks for the suggestion - the approach you suggest still involves the entire data set to be read in before it decides whether it qualifies for inclusion or not. With 10s of millions of records this approach is not desirable/possible..

regards

alexis

Re: Year command reading Oracle using Qlik ODBC Connector Package

Gysbert_Wassenaar — Mon, 25 Jan 2016 16:49:05 GMT

The first suggestion doesn't involve the entire data set since that where clause is part of the sql select statement.