Insert Into Fails...

Because I have a Unique Index on the table I'm Inserting Into: The table has a unique index on: udtype, udjoin, udfindex, udsort Begin Work; Lock Table root.udf In Exclusive Mode; Insert Into udf Select Distinct clnum As udjoin, 'CL' As udtype, 35 As udfindex, '' As udvalue, '95' As uddecimal, '' As uddate, 1 As udsort Fromclient; So, I get a message saying that the row cannot be added because it violates the Unique Index. I'm appending 14,000+ rows to my udf table. Can anyone tell me what I'm doing wrong here? Since my Select caluse included Distinct, that *should* make the each row unique right? Thanks, Steve Schroeder Merchant & Gould

Steve Schroeder wrote: > Because I have a Unique Index on the table I'm Inserting Into: > > The table has a unique index on: udtype, udjoin, udfindex, udsort > > Begin Work; > Lock Table root.udf In Exclusive Mode; > Insert Into udf Select Distinct clnum As udjoin, 'CL' As udtype, 35 As > udfindex, '' As udvalue, '95' As uddecimal, '' As uddate, 1 As udsort From> client; > > So, I get a message saying that the row cannot be added because it violates > the Unique Index. I'm appending 14,000+ rows to my udf table. > > Can anyone tell me what I'm doing wrong here? Since my Select caluse > included Distinct, that *should* make the each row unique right? There are several possibilities I can think of: * Target table is not empty at start up * The DISTINCT clause only ensures that all 7 columns together are unique; your 4 column sub-set is repeated on occasion. * You have managed to run across some sort of hash-collision in the locking system. I rate the second choice the most likely. I did run into the lock collision problem once upon a time about 7-8 years ago; the systems have been changed and improved since then, so you should not be seeing it. Of course, you don't say what version you are using... -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"