Archives of the TeradataForum

Message Posted: Mon, 15 May 2006 @ 09:55:12 GMT

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

You may need to specify a little more information.

It is pretty easy to recreate what you described, however, I have no idea if this is what you have encountered.

     /* Create a table that takes an integer as a primary index */

     Create table test (i1 integer) unique primary index (i1);

     /* Insert two distinct values */
     Insert into test values (11);
     Insert into test values (12);

     /* Create a second table with a char(10) as the UPI */
     create table test2 (c1 char (10), i1 integer)
     unique primary index (c1);

     /* Encounters a duplicate PI value error */
     insert into test2 (c1, i1)
     select i1, i1
     from test;

The above query encounters the duplicate PI value because it is casting the integer into a 10 character field. The problem arises because an integer requires 11 characters (a sign + 10 digits). With this cast, the least significant digit is lost resulting in two rows with the key value of ' 1'.

Interestingly if you remove the i1 column from test2, the insert succeeds but there is only one row in the table. This is because it is a set table and Teradata silently removes the duplicate rows (both rows consist of a single column with the same value ' 1').

I hope this helps

Glenn Mc

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