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*/
DECLARE v_salary INTEGER;
FOR for_test_loop AS ResultSet_VolatileCursor CURSOR FOR SELECT * FROM ddcoe_stg_test_tbls.s_test_dummy_Employees
SET v_salary = for_test_loop.Salary;
THEN UPDATE ddcoe_stg_test_tbls.s_test_dummy_Employees SET Salary = :v_salary*10;
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
The first & third errors are related to permissions. Looks like the procedure is created in a database other than ddcoe_stg_test_tbl. Ask your DBA's to grant select access to database.
grant select, update on ddcoe_stg_test_tbl.s_test_dummy_Employees to <DatabaseName in which stored procedure is created>;
If you think you are going to build more stored procedures like this, you can also ask DBA's to grant select/update on the full database. This however is sometimes not preferred because of security reasons.
You also need to use WHERE CURRENT OF clause so that it doesn't update all rows in the table and only updates the current row instead.
Yes, you need permissions, and you should be using WHERE CURRENT OF when applicable. But why are you allocating a dynamic result set? There is no output to this SP.
In any case, if this is all the SP has to do, then it should never be done in a cursor. For not only Teradata, but any relational database, it is more efficient to use set SQL if possible. In this case it is trivial. Instead of a cursor, just do:
SET Salary = Salary * 10
WHERE ( Salary <= 30000 ) ;
For more ideas about converting complex cursor logic to Set SQL, see http://developer.teradata.com/blog/georgecoleman