All Forums Analytics
prashanth.bose 4 posts Joined 03/10
16 Mar 2010
how to fetch rows from a teradata cursor when i am not sure about no of columns

Normally we do this :

SELECT employee_number , department_number FROM employee
WHERE employee_number < 1005 ORDER BY 1 ;

OPEN EmpCur;
FETCH EmpCur INTO emp, dept;
INSERT INTO emp_log VALUES (:emp, :dept);
SET newcount = newcount - 1;
UNTIL newcount = 0

my scenario is that i would be executing the query which is passed as a parameter and not sure how many columns are their:
how to FETCH the rows in this scenario...

Thanks in Advance....


Prashanth Bose
Adeel Chaudhry 773 posts Joined 04/08
01 Apr 2010


In such scenario, what you can do is .... count the columns, can be done my counting ',' between SELECT and FROM, before executing the query.

You will also have to take care about the data-types to fetch values to.

Just for knowledge .... cursors is perhaps the worst you can do with Teradata and its parallelism. Avoid it as much as you can .... and i believe there is no logic which can't be implemented in simple SQL rather implementing cursors. Though, it can be very hard and complex to do so.




-- If you are stuck at something .... consider it an opportunity to think anew.

SurK 4 posts Joined 09/12
26 Sep 2012

Hii Adeel, 
I want to automate User Management task using Teradata Store Procedure and for that I have created one SP which will perform error checks like username length, presence of user in database before a user defined macro will insert new user record into user maintenance table.
I have used Cursor in this Procedure which will do row by row processing but I feel that this row by row processing can hamper performance because this process will be schedule to run very frequently.
Could you please suggest me an alternative apporoach which can be use instead of Cursors? And how can I use it in inside the SP?
Thanks in advance.

You must sign in to leave a comment.