Data cleansing for that case should not require the original
file, from prior to those extraneous rows being inserted, if the
rules are explicit for the condition of duplicate rows; i.e.
duplicates being incorrect. With such a rule, a cleansing request
could fix any other similar incidents which had the same effect of
duplicate rows being posted. The /testing/ of the delete is
accomplished easily using isolation; i.e. just ROLLBACK if the wrong
rows were selected for the delete, where the journal or record locks
can be used to easily confirm. For any one account, that would
simply require adding the appropriate selection to the WHERE on the
DELETE; e.g. WHERE ACCOUNT='TEST'.
FWiW: Posted as a script versus words, someone could probably
post a SQL script in response which would effect easy correction.
As written, too much is left to the imagination. For example, a
script like the following might describe the scenario, yet this
specific script might instead just show how incorrectly one might
infer what was actually meant to be expressed in /words/ vs had the
details been scripted:
create table HF (HN INT, ACC INT
, DAT INT, TIM INT, IDP CHAR
, TXT CHAR(30)
, primary key (HN, ACC) )
;
create table TF (HN INT, ACC INT
, DAT INT, TIM INT, IDP CHAR
, TXN CHAR(30)
/* no unique key to prevent dup */ )
;
create view JLF (select H.DAT, H.TIM, H.IDP, H.TXT
, T.TXN
from HF H
inner join TF T
on (HN, ACC)
)
;
/* The error case, for one account: */
insert into HF values(7, 77, 090916, 111111, 'A', 'Oops')
;
insert into TF values
(7, 77, 090916, 111111, 'A', 'Oops')
,(7, 77, 090916, 111111, 'A', 'Oops')
,(7, 77, 090916, 111111, 'A', 'Oops')
;
Regards, Chuck
jmmckee wrote:
I made a mistake today. Accidentally ran a posting program three
additional times. <<SNIP words describing scenario>>
Can SQL be used to identify when there are multiple records for
the same hospital and account number that have identical date,
time, and poster id?
Can SQL be used to remove three of the four multiples?
Can SQL be used to do the previous two operations, but for one
specific account - for testing?
Would it be easier if the original posting file was available? My
assumption is yes. and that file can be available.
<<SNIP>>
As an Amazon Associate we earn from qualifying purchases.