Breaking down large query

[#1]

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.

[MarcoWedel]

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

[maxgro · Accepted solution]

replace the where clause

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

[#4]

Thanks. This worked.