Archives of the TeradataForum

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

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

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