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>>

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].