Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alexis
Partner - Specialist
Partner - Specialist

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
Gysbert_Wassenaar

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
Partner - Specialist
Partner - Specialist
Author

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

Gysbert_Wassenaar

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