[font=Verdana] Hi, I am cerating a procedure, for that I need to make a cursor's Select statment dynamic. e.g.
replace PROCEDURE spSample(in P_TAB varchar(10)) begin for cploop as cpcursor cursor for select * from p_tab do processing... end for; end;
But it results in error SPL1027:E(L12), Missing/Invalid SQL statement'E(3807):Object 'p_tab' does not exist.'.
I have tried by creating volatile/global temporary tables also but it results in same error. So is there any workaround to embed or dynamically replace the table name in FROM clause of cursor's SELECT ??
Unfortunately, I do know SELECT cannot be used with CALL. They are the keywords to be searched for. Because dynamic SQL is to be used for that, and for dynamic sql you have to use a stored-procedure named DBC.SysEXECSQL.
Depending on the details of what you are trying to accomplish, you might be able to do a insert/select into an interim table or so and then run your queries against it. (will work if the columns you will be selecting for any possible tables of interest are of the same data type).
If you can avoid them, cursors are usually a bad idea on Teradata, because you end up processing all the data in serial fashion and not using the parallel processing capabilities of the database. I would think very hard about looking at ways to do “processing…” in terms of the relational model. Build SQL statements to do the processing and you will find it works a lot faster. If you just want to use cursors it would be easier to write a host application instead.
But to answer your question you can’t make the select statement dynamic for a cursor in a stored procedure. The closest you can come is to define a dynamic result set which is available in TD12, which does allow a dynamic select statement. But that means you can only return the final result to the client using that technique. You cannot process it in a cursor for loop.
As was mentioned you can use the call dbc.sysexecsql to insert/select the original dynamic table into a temporary table and then have a cursor loop process that temporary table that has a fixed name.
I assume that the tables are all identical in their column layout and naming convention other than the name of the table, otherwise you will have further issues to deal with.
Yeah, you are right that I can’t make the select statement dynamic for a cursor in a stored procedure. I am using TD7.1 . But one point to be noticed that the tables are not all identical in their column layout.The input paramter(i.e. table name) for the procedure may can be always different table name. And, I need to process all records of this table in cursor for loop.Hence I need the table name to be embeded dynamically in that cursor's select statment.The hardcoded cursor select works well. Now for this dynamic case, I am looking to build SQL statments for processing. Your further suggestions are welcome.
I tried your suggestion regarding temporary table.but during compilation time that temp. table is not resolve its refernces. To coreect this, firstly I need to create table as a seperate unit and the select from this table. But my requiremnt is not like that.
replace procedure test(in p_tab varchar(20)) begin CALL USER01.SYSEXECSQL('CREATE GLOBAL TEMPORARY TABLE TMP as ' ||p_tab|| 'on commit preserve rows'); CALL USER01.SYSEXECSQL('INSERT INTO TMP SELECT * FROM '||p_tab); for c1 as cur cursor for select * from tmp do insert into a values ('harshad'); end for; end;
error: SPL1027:E(L7), Missing/Invalid SQL statement'E(3807):Object 'tmp' does not exist.'.
(as I am selecting duting compilation time, I am getting this error).
If you have any suggestions,please do let me know.