Archives of the TeradataForum

Message Posted: Thu, 14 Dec 2006 @ 19:51:43 GMT

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

<-- Anonymously Posted: Thursday, December 14, 2006 14:50 -->

We currently have a table with several columns in it. Below is the table description for some of the columns:

     Create set table acct, no fallback,
                 No before journal,
                 No after journal,
                 Checksum = Default
     (
     Acct_SID  DECIMAL(18,0) NOT NULL GENERATED ALWAYS AS IDNETITY
                 (START WITH -999999999999999999
                 INCREMENT BY 1
                 MINVALUE -999999999999999999
                 MAXVALUE -990000000000000000
                 NO CYCLE),
     Source_ID_1  VARCHAR(30)  CHARACTER SET LATIN NOT CASESPECIFIC,
     Source_ID_2  VARCHAR(30)  CHARACTER SET LATIN NOT CASESPECIFIC)
     UNIQUE PRIMARY INDEX ACCT_SID_UP (Acct_SID)

The issue we are running into is that the Acct_SID is being duplicated. There are only 30 million records in this table, so it's not that we are running out of numbers. The loads are not failing, etc. We found this problem because we did the following query:

     Select * from Acct where source_id_1 = '1'
     UNION
     Select * from Acct where source_id_1 = '2'

The above query returned 2 rows, with the source_id as we had specified in the query, but the Acct_Sid is exactly the same(- 999999999902734210). The source_id_1 field is a unique number in our source system, so for each one of these records, there should be a distinct Acct_Sid.

However, if you perform the query select * from acct where acct_sid = -999999999902734210, it only returns one of the records.

My questions are:

How did the Acct_Sid field get duplicated if it is generated always and there is a unique primary index on that field?

How do we fix this problem so that the Acct_Sid is not duplicated?

Thanks for the help.

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