HANA SQL WHERE returns 6931 Error

[gregdelima]

0 favorite

I'm using the following SQL script:

SELECT EXTRACT(YEAR FROM FPCOMMNPERIOD_CALC) As COMMYEAR
FROM "_SYS_BIC"."tfx.adhoc.Greg_Test/CV_SALES_W_PRICINGCV_TEST"
GROUP BY EXTRACT(YEAR FROM FPCOMMNPERIOD_CALC)
ORDER BY EXTRACT(YEAR FROM FPCOMMNPERIOD_CALC) ASC;

and it operates just fine, outputting a list from 2007 to 2018. However, if we include the below line:

WHERE EXTRACT(YEAR FROM FPCOMMNPERIOD_CALC) IN (2011, 2012, 2013)

to subset certain years, HANA returns:

Could not execute 'SELECT EXTRACT(YEAR FROM FPCOMMNPERIOD_CALC) As COMMYEAR FROM ...' in 114 ms 271 µs . 
[303]: invalid DATE, TIME or TIMESTAMP value: search table error:  [6931] attribute value is not a date or wrong syntax

I'm using Tableau to connect to HANA, which generates a similar filtering statement. I've tried even filtering for 1 value as text = '2018' but HANA continues to output the error.

The column FPCOMMNPERIOD_CALC is a calculated column, where ZTFPCOMN is for e.g. '2018007' (2018 period 007).

leftstr("ZTFPCOMN",4)+'-'+rightstr("ZTFPCOMN",2)+'-'+'01')

[TuncayKaraca]

Hi Greg,

It seems EXTRACT needs be date <d> field. EXTRACT Function (Datetime)

You may take a look into converting FPCOMMNPERIOD_CALC to date type first with using EXTRACT. Possibly TO_DATE Function (Data Type Conversion)

[gregdelima]

Hi Tuncay,

The field is being cast as a date in both the calculated column, and at the semantic layer. Furthermore, as noted, the other instances of EXTRACT function as expected, however, cease to function in the WHERE clause.

Thanks,

[SergioG_TX]

once the field is casted as a DATe ... have you tried...

WHERE YEAR(YOUR_DATE_COL) = (2010,2011,2012);  -- so the YEAR(<SOME_DATE> function extracts the date from the date value - hope this works for you

[gregdelima]

Thanks Sergio,

When I update to

WHERE YEAR(FPCOMMNPERIOD_CALC) = 2015

IT continues to return:

[303]: invalid DATE, TIME or TIMESTAMP value: search table error:  [6931] attribute value is not a date or wrong syntax

[SergioG_TX]

so your FPCOMMNPERIOD_CALC field doesnt seem to be a date... try this

WHERE YEAR(TO_DATE(FPCOMMNPERIOD_CALC)) = 2015