Issue with DAYOFWEEK in a Teradata Procedure

Database
Enthusiast

Issue with DAYOFWEEK in a Teradata Procedure

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

2 REPLIES
Junior Supporter

Re: Issue with DAYOFWEEK in a Teradata Procedure

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:

http://carlosal.wordpress.com/2010/06/24/day-of-week-en-teradata/

HTH.

Cheers.

Carlos.

Enthusiast

Re: Issue with DAYOFWEEK in a Teradata Procedure

Thanks Carlos, it worked for me.