how to fetch rows from a teradata cursor when i am not sure about no of columns

Analytics

how to fetch rows from a teradata cursor when i am not sure about no of columns

Normally we do this :

DECLARE EmpCur CURSOR FOR
SELECT employee_number , department_number FROM employee
WHERE employee_number < 1005 ORDER BY 1 ;

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

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
2 REPLIES
Teradata Employee

Re: how to fetch rows from a teradata cursor when i am not sure about no of columns

Hi,

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.

HTH!

Regards,

MAC
Fan

Re: how to fetch rows from a teradata cursor when i am not sure about no of columns

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.