All Forums UDA
gavakie 9 posts Joined 12/08
02 Mar 2009
getting an error on this code

Im getting an error expecting something between year and i dont understand whats causing it.SELECT *FROM edw_access_views.calendar calWHERE cal.calendar_date = YEAR(CURRENT_DATE)||'-'||'01'||'-'||'01' ) AND ( SELECT MAX(calendar_date) FROM edw_access_views.calendar cal WHERE (cal.calendar_date BETWEEN YEAR(CURRENT_DATE)||'-'||'01'||'-'||'01' AND CURRENT_DATE) ) OR cal.calendar_date BETWEEN ( SELECT MIN(calendar_date) FROM edw_access_views.calendar cal WHERE (cal.calendar_date BETWEEN YEAR(CURRENT_DATE)-1||'-'||'01'||'-'||'01' AND CURRENT_DATE-365) ) AND ( SELECT MIN(calendar_date) FROM edw_access_views.calendar cal WHERE (cal.calendar_date BETWEEN YEAR(CURRENT_DATE)-1||'-'||'01'||'-'||'01' AND CURRENT_DATE-365) )

Fred 1096 posts Joined 08/04
02 Mar 2009

YEAR is not a Teradata function. Use EXTRACT(YEAR FROM CURRENT_DATE).

gavakie 9 posts Joined 12/08
03 Mar 2009

I dont understand if I run Year(current_date) i return 2009.

gavakie 9 posts Joined 12/08
03 Mar 2009

WHERE (cal.calendar_date BETWEEN SELECT ADD_MONTHS(CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE) + 1,-MONTH(CURRENT_DATE)+1) AND CURRENT_DATE OR cal.calendar_date BETWEEN SELECT ADD_MONTHS((CURRENT_DATE-365)-EXTRACT(DAY FROM (CURRENT_DATE-365)) + 1,-MONTH((CURRENT_DATE-365))+1) AND CURRENT_DATE-365)I tried this as well and I get the expecting something error.

Fred 1096 posts Joined 08/04
03 Mar 2009

You are connecting via ODBC with "parsing" (also called Use of ODBC Extensions) enabled. YEAR, MONTH, etc. are ODBC functions, and in simple cases the driver can fix the SQL to be Teradata compatible and/or fix the answer once it is returned to the client. But when used as part of a more complex expression, it can get confused.

You must sign in to leave a comment.