Archives of the TeradataForum

Message Posted: Fri, 20 Feb 2004 @ 12:21:42 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Ron,

at the first look you should be able to convert it to two Teradata SPs without major problems, because it's almost ANSI SQL.

Some remarks:

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.

But....

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...

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>