Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 03 Apr 2012 @ 10:47:19 GMT


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


Subj:   Re: Volatile tables within Stored Procedures
 
From:   McCall, Glenn David

Hi Mark,

You have encountered the classic which one comes first problem.... The horse of the cart? The chicken or the egg? The table or the insert????

Here is your procedure for reference:

     Replace Stored Procedure db.sp1()
     Begin
     Create volatile table tmp_table
      (numid int)
     on commit preserve rows;

     insert into tmp_table
     select numid
     from db1.table;

     End;

The procedure fails to compile because the compile process is attempting to validate the insert query. But the table (tmp_table) does not exist **when the procedure is compiled**. So the compiler quite correctly tells you that the insert is invalid.

**We** know that the procedure will create the table and therefore the insert will be just fine - but the compiler doesn't work like that.

You have two options.

a) Create the table (volatile should be fine) first then compile the procedure (you can then drop the table and run the procedure). Or...

2) Use "dynamic SQL" for the insert. (call dbc.sysexecsql('insert into tmp_table ...');


Good luck. :-)

Glenn Mc



     
  <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