FOR cursor in teradata SP

Database
N/A

FOR cursor in teradata SP

Hi All,

I'm new to Teradata and was trying out the For cursor in a store procedure. I'm getting the below errors. Please help me to identify the issue with my code.

SPL1024:E(L349), Unexpected text '' in place of DO.

SPL1048:E(L349), Unexpected text ';' in place of SPL statement.

SPL1060:E(L352), Unexpected text 'DO' in place of END IF / END FOR / END WHILE / END REPEAT / END LOOP.

SPL1027:E(L362), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like ';' between ';' and the 'IF' keyword.'.

SPL1027:E(L363), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like ';' between the 'END' keyword and the 'IF' keyword.'.

Below is my store procedure code:

REPLACE PROCEDURE SP_name ( )

    BEGIN

    DECLARE END_TS TIMESTAMP(0);

             /*=========================================================================================

        Loop through each record in summry table and calucalte the  time

        =========================================================================================*/ 

   SET NEW_TS = '1900-01-01 00:00:00';

   FOR rec AS ctestS

                CURSOR FOR 

   SELECT   

    START_time,

    END_time,

    cal_time,

    END_TS

         FROM  table_name;          

DO

IF NewTS > rec.cal_time

THEN 

UPDATE table_name  

SET cal_time =rec.NewTS 

WHERE filed1= rec.filed1;

END IF;

IF rec.END_TS NOT NULL THEN 

UPDATE  table_name

SET END_time =rec.END_TS

WHERE filed1= rec.filed1;

END IF;

SET NewTS = rec.END_time;

  END FOR;

        END;

Appreciate your help. Thanks.

5 REPLIES
Teradata Employee

Re: FOR cursor in teradata SP

There should not be a semicolon between the SELECT statement and the DO keyword.

Also there is no rec.NewTS or rec.filed1 since you didn't SELECT those columns in your cursor.

N/A

Re: FOR cursor in teradata SP

Hi Fred, Thanks for your reply.

I removed the semicolon and the rec.NewTS. Now I'm getting another error.

"Referring to undefined alias 'NewTS'."

I have declared this field in the begin. Please advice. 

REPLACE PROCEDURE SP_name ( )

     BEGIN

    DECLARE NewTS TIMESTAMP(0);

 / *===================================================== 

        Loop through each record in summry table and calucalte the  time

=======================================================*/ 

   SET NewTS = '1900-01-01 00:00:00';

   FOR rec AS ctestS

                CURSOR FOR 

   SELECT  

     field1,

    START_time,

    END_time,

    cal_time,

    END_TS

         FROM  table_name          

 DO

IF NewTS > rec.cal_time

THEN 

UPDATE table_name  

SET cal_time =NewTS 

WHERE filed1= rec.filed1;

END IF;

 

IF rec.END_TS IS NOT NULL THEN 

UPDATE  table_name

 

SET END_time =rec.END_TS

WHERE filed1= rec.filed1;

END IF;

SET NewTS = rec.END_time;

END FOR;

 END;

Teradata Employee

Re: FOR cursor in teradata SP

Your UPDATE statement needs a colon to indicate that NewTS is a variable rather than a column name.

UPDATE table_name

SET cal_time = :NewTS

N/A

Re: FOR cursor in teradata SP

I tried , but still getting the same error. Another suggestion.

Thanks,

Vasanthy

Teradata Employee

Re: FOR cursor in teradata SP

Additional fixes applied below:

Use a timestamp literal to set the initial value of NewTS instead of a character literal.

Spell "field1" consistently.

REPLACE PROCEDURE SP_name ( )

     BEGIN

    DECLARE NewTS TIMESTAMP(0);

 /*=====================================================

        Loop through each record in summary table and calculate the  time

=======================================================*/

   SET NewTS = timestamp'1900-01-01 00:00:00';

   FOR rec AS ctestS

                CURSOR FOR

   SELECT 

     field1,

    START_time,

    END_time,

    cal_time,

    END_TS

         FROM  table_name         

 DO

IF NewTS > rec.cal_time

THEN

UPDATE table_name 

SET cal_time = :NewTS

WHERE field1= rec.field1;

END IF;

IF rec.END_TS IS NOT NULL THEN

UPDATE  table_name

SET END_time =rec.END_TS

WHERE field1= rec.field1;

END IF;

SET NewTS = rec.END_time;

END FOR;

 END;