Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 11 Mar 2005 @ 22:39:40 GMT


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


Subj:   Using Teradata Procedural Language
 
From:   Srikanth Goli

Thanks to all for your replies regarding batch deletion. I have a follow up question..

Is there any way to declare variables and perform adhoc queries that involve loops iterations etc,. from the Teradata SQL Assistant? For example to delete rows from a table in batches, I would like to do this (delete 20% of rows at a time) :

     declare modctr integer ;
     set modctr = 0;

     WHILE modctr  < 5
     DO

            begin transaction ;
            delete   from Location  where Location_ID mod 5 = :modctr ;
            end transaction;

            SET modctr = modctr +1 ;
     END WHILE;

This does not work since the Declare is not allowed.. The SQL can be converted as a procedure or a macro, but I do not have the permissions in prd.. In this specific case I can make 5 sql statements, but what if the iterations are much larger? Any Teradata client tool allows adhoc SQL with teradata procedural extensions?

On a different note, is transactional control required (begin & commit tran) for the above delete statement or every delete batch committed automatically?


thanks.



     
  <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