All Forums Database
krishr 3 posts Joined 12/15
08 Dec 2015
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.

Fred 1096 posts Joined 08/04
09 Dec 2015

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.

krishr 3 posts Joined 12/15
09 Dec 2015

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;

Fred 1096 posts Joined 08/04
10 Dec 2015

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

krishr 3 posts Joined 12/15
10 Dec 2015

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

Fred 1096 posts Joined 08/04
10 Dec 2015

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;

You must sign in to leave a comment.