Archives of the TeradataForum
Message Posted: Fri, 20 Feb 2004 @ 12:21:42 GMT
at the first look you should be able to convert it to two Teradata SPs without major problems, because it's almost ANSI SQL.
Are you running in Teradata session mode or ANSI mode? In Teradata mode there's an autocommit.
cursor cur is select shop_nbr from igor_elig_stores; -> cursor
declaration is only allowed in V2R5, but you can simply put the select in the for clause:
for select shop_nbr from igor_elig_stores do.... execute immediate(...) -> CALL dbc.SysExecSQL(...);
No need for dynamic SQL with the following:
truncate table old_igor_tool_result -> delete from old_igor_tool_result; lerner_metric_old_tool.pResult(...) -> CALL lerner_metric_old_tool.pResult(...);
And those intermediate tables could/should be replaced by Volatile/Globa Temporary Tables.
You're running that procedure for each shop, so the main question is: How many shop_nbrs exist (and how many rows in each table)?
You're just doing some simple aggregations, there's no need to repeat all those seperate steps over and over again.
So this can probably be replaced by a *single* query or at least a set of queries in a macro. This should provide a much faster execution...
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|