Archives of the TeradataForum

Message Posted: Wed, 12 Mar 2008 @ 10:31:26 GMT

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

Hi David,

"NOT FOUND" is used for when data is not found (related to DML transactions). I might be wrong (haven't checked) but this probably does not cover SQLSTATE codes for objects that do not exist.

Perhaps you could follow one of 2 approaches:

1) First Select from dbc.tables to see if table exists, before doing the DML.

2) Check the specific :SQLSTATE variable from within a more generic exception handler such as "SQLEXCEPTION"

Also, use a proper "BEGIN .... END" construct for the specific code segment:

     BEGIN
          DECLARE EXIT HANDLER /* or a continue handler, dependig on
          the approach */
          FOR SQLEXCEPTION
          BEGIN
              >>>>>your code to check the :SQLSTATE variable and
                   create table if required
          END;
              >>>>>your other code to do DML (please keep in mind
                   the "retry" situation if table needed to be created)
     END;

Due to the issues with retrying the DML operation when the table actually had to be created, the approach of first checking dbc.tables is probably the more elegant way of doing this.

!!!EVEN BETTER!!!: Try and create the table irrespective of whether it exists or not (and handle any failure of the create attempt). Then run the DML. This is a much more robust approach from a batch processing point of view. But DO check the value of :SQLSTATE to ensure you know what you are dealing with.

Kind regards

Etienne Stieger
Analytics and Information Management (AIM/EIW)
Standard Bank of South Africa Ltd

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