Archives of the TeradataForum

Message Posted: Tue, 11 Mar 2008 @ 16:17:42 GMT

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

I'm trying to create the code in a Stored Procedure such that it will Create a Table if it doesn't already exist.

I've tried several combinations of Continue Handling but, even when I get something to compile, it then fails to pick up the Exception (which should Create the Table).

Within the code I have these simple two statements :

     -- Set up the Renum_Cand Table
     DELETE FROM RENUM_CAND;
     INS INTO RENUM_CAND SEL * FROM RENUM_SET;

I'd like to be in a position where, if the Renum_Cand(idate) table doesn't exist - detected by the DELETE - it will then Create the Table in the Continue Exception and then proceed with the Insert.

Has anyone got a good example of something that would compile and actually work ?

I did code this early in the Procedure :

        DECLARE CONTINUE HANDLER
              FOR NOT FOUND
                      CREATE GLOBAL TEMPORARY TABLE RENUM_CAND
                      (OLD_ACCT_ID CHAR(10) NOT NULL, NEW_ACCT_ID CHAR(10))
                      UNIQUE PRIMARY INDEX (OLD_ACCT_ID)
                      ON COMMIT PRESERVE ROWS;

but then it either wouldn't compile or it failed to pick it up (detecting the more global DECLARE EXIT HANDLER FOR SQLEXCEPTION handler I've also got in the procedure).

I'm at the point of giving up with specific Exception Handling but, having had good experiences with Oracle (spit) recently, I thought to myself "I'm sure Teradata can do at least as good". Please, can anyone prove with right ?

Regards to all,

Dave Clough
Database Designer
Express ICS

www.tnt.com

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