topic Re: Breaking down large query in QlikView

Breaking down large query

Sun, 07 Sep 2014 11:27:53 GMT

The query I was using started giving me error due to the size. I am using ODBC to get data from Oracle and started getting an error not enough space in Temp.

I am trying to break it down into pieces and then combining into one qvd using the following code:

Let y = year(today());

For x=2008 to $(y)

Med_Clm_Data:

LOAD *,

TYPE&' - '&CODE AS ACTIVITY_CODE;

SQL SELECT *

FROM MIMS."CLAIM_HISTORY_VU_NU"

where Year(TREAT_FROM) = $(x);

next x

Store Med_Clm_Data into [..\QlikviewSource\Med_Clm_data.qvd] (qvd);

However, now it is giving me error saying that - [Oracle][ODBC][Ora]ORA-00904: "Year": Invalid Identifier

Would appreciate some help as to what is wrong with the code.

Re: Breaking down large query

MarcoWedel — Sun, 07 Sep 2014 12:43:15 GMT

seems to be an oracle and no QV error.

Maybe Year is case sensitive? Then try YEAR.

Or something like

where EXTRACT(year FROM TREAT_FROM) = $(x);

hope this helps

regards

Marco

Re: Breaking down large query

maxgro — Sun, 07 Sep 2014 17:10:24 GMT

replace the where clause

where to_char(TREAT_FROM, 'YYYY') = $(x);

Re: Breaking down large query

Wed, 10 Sep 2014 05:57:57 GMT

Thanks. This worked.