Hi - We have a table which has 3 columns pattern, tdquery and pattern_value.
for example (LastCalanderDayofNextMonth)-
LCDOFNM_MMDD CAST((ADD_MONTHS((DATE - EXTRACT(DAY FROM DATE)+1),2)-1 (FORMAT 'MMDD')) AS CHAR(4)) 0630.
There is a Proc. which loops thru this table and updates the pattern_value. As of now, i'm having an issue with LASTWEEKFRIDAY_DDMM and the TDQuery i have is CAST(((DATE-DAYOFWEEK(DATE)-1) (FORMAT ''DDMM'')) AS CHAR(4)).
REPLACE PROCEDURE SP_Populate_Metadata(OUT returnCode SMALLINT)
MAIN:BEGIN -- Main BEGIN -- BEGINS Here
DECLARE ProcName VARCHAR(100) DEFAULT 'SP_Populate_Metadata';
DECLARE Sqlstr VARCHAR(1000);
DECLARE iRC INTEGER DEFAULT 0;
DECLARE sSQLErrorState CHAR(5) DEFAULT '00000';
DECLARE scode INTEGER;
DECLARE EXIT HANDLER
SET sSQLErrorState = SQLSTATE;
SET SCODE = SQLCODE;
FOR C2 AS CUR CURSOR FOR SELECT PATTERN, TDQUERY FROM METADATA_PATTERN ORDER BY PATTERN DESC
SET SQLSTR = 'UPDATE METADATA_PATTERN SET PATTERN_VALUE=( ' || C2.TDQUERY || ' ) WHERE PATTERN= ' || ''''||C2.PATTERN||'''';
INSERT INTO ERROR_CHECKING_TBL VALUES(:ProcName,NULL,NULL,NULL,NULL,SQLSTR,NULL,NULL,CURRENT_TIMESTAMP(4));
EXECUTE IMMEDIATE SQLSTR;
The Update Statement which is formed is
UPDATE METADATA_PATTERN SET PATTERN_VALUE=( CAST(((DATE-DAYOFWEEK(DATE)-1) (FORMAT 'DDMM')) AS CHAR(4)) ) WHERE PATTERN= 'LASTWEEKFRIDAY_DDMM'. This Update works fine when run from Sql Assistant but for some reason doesnt work from the Proc.
I am thinking this could be a problem with DAYOFWEEK but i could be wrong.
Can you Please help.
DAYOFWEEK is an ODBC extension (thus works in SQLA), but not a Teradata Function (well... pre 14.0 ;-) ).
You can find an example of how calculate the DAY OF WEEK here: