Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Converting Oracle PL/SQL to run with Teradata DW
 
From:   Dieter Noeth

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016