RE: Duplicate Keys

Hi James

How is the record written, i.e. is this a piece of SQL or is it an RPG I/O.

Also is this time field, a proper datetime field or is it a packed decimal
field.



If both are the later there might be something a bit sneaky you could do.

Neill
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Salter, James
Sent: 08 December 2008 21:12
To: midrange-l@xxxxxxxxxxxx
Subject: Duplicate Keys

Neill, it looks like I may have two options:

1) There is an inventory update program that is the central program that
would create the duplicates. I could check each time to verify that the
newly created record doesn't already exist (as a key) or I could delay
the job one second to create the unique-ness.

2) Inside a BEFORE INSERT trigger program check to see if the duplicate
exists (before writing the second record). If it does, then delete the
1st record & rewrite it with a time of (time - 1) second. Then create
the new record.

Thanks for your help. The 2nd would be the better option if there are
multiple programs creating these records.

James Salter
Systems Programmer
American Cast Iron Pipe Company
phone (205) 325-3033
fax (205) 307-3833

------------------------------

message: 3
date: Mon, 8 Dec 2008 20:36:50 -0000
from: "Neill Harper" <neill.harper@xxxxxxxx>
subject: RE: Duplicate Keys

Hi James

Would it be possible to create a logical over this file that is keyed,
and
do your updates to the file via the logical?

Neill

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Salter, James
Sent: 08 December 2008 16:50
To: midrange-l@xxxxxxxxxxxx
Subject: Duplicate Keys

Hello, has anyone ever handled the following situation:

I have a history file that is not uniquely keyed (changing this would be
a nightmare).

This file is synchronized with another system and duplicate key errors
are sometimes received when trying to update records. This is because
some history records are created with the same key of item, date, &
time.

Is there a way via a BEFORE INSERT trigger (or some other process) that
I can change or prevent the 2nd record from being written if it is a
duplicate?

I can increment the time on the new record by a fraction of a second as
long as I can intercept it somehow.

Thanks.
James Salter
Systems Programmer
American Cast Iron Pipe Company
phone 205-325-3033
fax 205-307-3833
_________Confidentiality Notice_______________________
This e-mail and any files transmitted with it is
confidential and is intended solely for the use of
the individual(s) or entity(ies) to whom this e-mail
is addressed. If you are not the intended recipient
or the person responsible for delivering the e-mail
to the intended recipient, be advised that you have
received this e-mail in error, and that any use,
disclosure, dissemination, forwarding, printing,
retention or copying of this e-mail is strictly
prohibited. If you have received this e-mail in
error, please immediately return this e-mail to
the sender and delete the e-mail from your system.
Thank you.