All Forums Database
Chiranjib Nandy 2 posts Joined 09/14
04 Nov 2014
How to define a cursor in Teradata and loop through the result set ?

Hi ,
I want to create a test cursor and loop through the result set performing specific computations.
What have I tried till now is something like this 
 

REPLACE PROCEDURE SP_VolatileCursor()
DYNAMIC RESULT SETS 1   /* The number of strings that will be returned to the caller or the client running the SP*/
BEGIN
    DECLARE v_salary INTEGER;
	FOR for_test_loop AS ResultSet_VolatileCursor CURSOR FOR SELECT * FROM ddcoe_stg_test_tbls.s_test_dummy_Employees
	DO
		SET v_salary = for_test_loop.Salary;
		IF(v_salary<=30000)
			THEN UPDATE ddcoe_stg_test_tbls.s_test_dummy_Employees SET Salary = :v_salary*10;
		END IF;
	END FOR;
		
			
END;

If the salary of an Employee is less than 30000 INR I want to tenfold the salary .
But I am getting a few errors

SPL1027:E(L6), Missing/Invalid SQL statement'E(3523):An owner referenced by user does not have SELECT access to ddcoe_stg_test_tbls.s_test_dummy_Employees.'.
SPL1031:E(L7), Referring to undefined alias 'Salary'.
SPL5000:W(L9), E(5315):An owner referenced by user does not have UPDATE access to ddcoe_stg_test_tbls.s_test_dummy_Employees.Salary.

What am I missing ? Please Explain

Tags:
You must sign in to leave a comment.