If an error occurs when you validate a stored procedure in Teradata while
the query does run successfully in Teradata SQL Assistant, the root cause is probably originated in the fact that SQL Assistant uses the Teradata ODBC Driver, which parses your SQL request and rewrites it. In contrast, the stored procedure does not parse or rewrite your SQL request. Instead, it sends your unchanged SQL request to the Teradata Database.
Example: The Teradata ODBC Driver implements the MONTH function.
The Teradata Database does not provide a MONTH function. You need to change your query to use the SQL syntax that is supported by the Teradata Database. Specifically, you must use the EXTRACT(MONTH FROM) function.
In your query, you must replace
MONTH(CURRENT_DATE)
(that results in 'E(3706):Syntax error: expected something between the '(' keyword and the 'month' keyword')
with
EXTRACT(MONTH FROM CURRENT_DATE)
(mutatis mutandis for YEAR, QUARTER, WEEK). Also if you try do run the query via JAVA, the JAVA Driver will send you query unchanged to the database).
