Archives of the TeradataForum
Message Posted: Thu, 14 Dec 2006 @ 19:51:43 GMT
<-- 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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|