Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
alexis
Contributor III

Year command reading Oracle using Qlik ODBC Connector Package

Hi

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

3 Replies
MVP & Luminary
MVP & Luminary

Re: Year command reading Oracle using Qlik ODBC Connector Package

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"


talk is cheap, supply exceeds demand
alexis
Contributor III

Re: Year command reading Oracle using Qlik ODBC Connector Package

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

MVP & Luminary
MVP & Luminary

Re: Year command reading Oracle using Qlik ODBC Connector Package

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


talk is cheap, supply exceeds demand
Community Browser