Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 01 Apr 2008 @ 10:51:58 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   FOR cursor in teradata
 
From:   gagan.bharadia

Hi Everyone,

Below mention is procedure with FOR cursor while commented is oracle procedure need to convert into teradata

     replace PROCEDURE pro.name ()
     BEGIN
     DECLARE variable1 DATE; declare variable2 integer;

(oracle cursor)------- CURSOR test IS

     SELECT DISTINCT column_name
       FROM table_name1
       WHERE column_name2= 'Y'
     GROUP BY column_name1
     HAVING COUNT(*)>1;

Change to teradata using FOR cursor declaration

     FOR REC AS test CURSOR FOR
     SELECT DISTINCT column_name1
     FROM table_name1
     WHERE column_name2= 'Y'
     GROUP BY column_name1
     HAVING COUNT(*)>1;
     DO
     SET variable1 = REC.column_name1;
     Rest of select and update statements
     END LOOP; END;

The problem is after FROM in select statement ,it is not accepting WHERE clause( neither after FROM clause nor after DO clause).

So my query ,if we need to use where condition with SELECT clause of FOR CURSOR than what is the way to use it.I am getting these below mentioned error while execution.

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

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

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

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


Thanks & Regards

Gagan Bharadia
Wipro Infotech



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016