Hi,

I am getting a fatal error as Error 100(Duplicate value) on one customised table tcsec054. Table has one primary index and 10 more duplicate indices.
this error is coming while in case of db.update not db.insert.
i checked in debug mode, retry is happening 10 times and at the end fatal error coming.
i checked multiple things but not helping.
this is the source code,

db.retry.point()

select tcsec054.*
from tcsec054 for update
where tcsec054._index1 = {:location}
selectdo
tcsec054.nama = tccom013.nama
tcsec054.namb = tccom013.namb
tcsec054.namc = tccom013.namc
tcsec054.namd = tccom013.namd
tcsec054.city = city
tcsec054.stat = state
tcsec054.pstc = tccom013.pstc
tcsec054.ccty = tccom013.ccty
tcsec054.cnst = cnst
tcsec054.lcst = loc.status
tcsec054.cplt = price.list
tcsec054.crep = sales.rep
tcsec054.creg = area
tcsec054.cfcg = cfcg
tcsec054.cste = cste
tcsec054.bloc = frm.bloc
db.update(ttcsec054, db.retry, e)
if not e then
commit.transaction() |* Fatal Error coming at this line
endif
selectempty
tcsec054.cloc = location
tcsec054.cuno = tccom013.cuno
tcsec054.cdel = tccom013.cdel
tcsec054.nama = tccom013.nama
tcsec054.namb = tccom013.namb
tcsec054.namc = tccom013.namc
tcsec054.namd = tccom013.namd
tcsec054.city = city
tcsec054.stat = state
tcsec054.pstc = tccom013.pstc
tcsec054.ccty = tccom013.ccty
tcsec054.cnst = cnst
tcsec054.lcst = loc.status
tcsec054.cplt = price.list
tcsec054.crep = sales.rep
tcsec054.creg = area
tcsec054.cfcg = cfcg
tcsec054.cste = cste
tcsec054.bloc = frm.bloc
db.insert(ttcsec054, db.skip.dupl, e)
if not e then
commit.transaction()
endif
endselect

Thanks in advance.

What is the primary index? It almost looks like you are looking for location, which could return multiple records. From the code it looks like tcsec054.cloc, tcsec054.cuno,
tcsec054.cdel are part of the index.

Thanks Mark for quick reply.
Primary Index is only one field tcsec054.cloc.

Location variable is mapped with index.
Location is populated as,
location(1;6) = tccom013.cuno
location(7;1) = "-"
location(8;3) = tccom013.cdel

Interesting - if the primary index is only location then I am not sure why it would come up with duplicate transaction. Are you sure all the other indexes allow duplicates? Is there anything in any of the logs that shows the error.

I think may be this is because of data issue. It is not happening for all records.
I checked the error logs, it only says about error. Any details are not given.
I am thinking about alternative solution that delete the record and insert it again.

You can try if it helps to reorganize the table.

I think may be this is because of data issue. It is not happening for all records.
I checked the error logs, it only says about error. Any details are not given.
I am thinking about alternative solution that delete the record and insert it again.
Not sure that will work - you are doing an update and something is creating that duplicate error. To me it has to be one of the other indexes not allowing duplicates. Deleting the record you find and basically re-inserting the record again (new information) will still create a duplicate record error. Unless like JaapJD suggests the table or indexes might be corrupt in some way. We had this happen once or twice - but the errors we got were basically cannot find the record using indexes. Been a really long time since we have seen something like that.

If its a DEV/TEST Environment, you can take a backup of your data, delete and recreate the table. Restore the data and see if it works for you.

Second step would be to reorganize the indices as Jaap mentioned. Index change can cause several data issue which are not easy to identify just by looking at the data.