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)).

Procedure:

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

FOR SQLEXCEPTION

BEGIN

SET sSQLErrorState = SQLSTATE;

SET SCODE = SQLCODE;

END;

FOR C2 AS CUR CURSOR FOR SELECT PATTERN, TDQUERY FROM METADATA_PATTERN ORDER BY PATTERN DESC

DO

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;

END FOR;

SET returnCode=iRC;

END MAIN;

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.

Thanks,
Vinay