Claudine, indexes are really simple to use, and speed up processing by a large factor. So, first create a couple of indexes on the main table. This code snippet should give you what you need. I think you will be pleased with how quickly R:Base will return the duplicate record set.
CREATE INDEX DupCheck ON d_invoice_trucks + (carrier, origination_3, destination_3, service_dt) CREATE INDEX DupFilter ON d_invoice_trucks (inbox_state) CREATE VIEW vwDupCheck + (carrier,origination_3,destination_3,service_dt) AS + SELECT carrier,origination_3,destination_3,service_dt + FROM d_invoice_trucks WHERE inbox_state IN (2,4) + GROUP BY carrier,origination_3,destination_3,service_dt + HAVING COUNT (*) > 1 SELECT * FROM vwDupCheck will give you the duplicate records. --- Claudine Robbins <[EMAIL PROTECTED]> wrote: > > Hi gang, > > I need to audit records in a table against the records in that same table. > Specifically 4 fields in different combinations that I need to identify as > being duplicates, not to ignore them (as is usually the case with > duplicates) but to record them and show them to the user. > > This is what I have tried so far with the end result being the now famous > 'database non responding' message. > > 01. I have a subset of the records in a temporary table (audittrucks) to > identify the currently auditable records (inbox_state = 2 or 4 in d_inbox) > 02. I create an empty table to record the errors found (carriererror) > 03. With a cursor, I go through the records in the table created in 01 to > compare to the permanent table (d_invoice_trucks) and write records I find > in the table created in 02. > 04. Then, I bomb... > > I don't know how to use indices so I'm not. There are over 32,000 records > in d_invoice_trucks. Can anyone see what I'm doing wrong? TIA ~Claudine > > Here is my routine: > > PROJECT TEMPORARY audittrucks FROM d_invoice_trucks USING ALL + > WHERE invoice_id IN (SELECT invoice_id IN d_inbox WHERE inbox_state IN > (2,4)) > > PROJECT TEMPORARY carriererror FROM d_invoice_trucks USING + > Carrier, origination_3, destination_3, service_dt WHERE COUNT = 0 > > DROP CURSOR c1 > DECLARE c1 CURSOR FOR SELECT carrier, origination_3, destination_3, > service_dt + > FROM audittrucks > OPEN c1 > FETCH c1 INTO vcarrier INDICATOR iv1, vorigin_3 INDICATOR iv3, vdestin_3 > INDICATOR iv4, vscedt INDICATOR iv5 > > WHILE SQLCODE <> 100 THEN > > INSERT INTO carriererror (carrier, origination_3, destination_3, > service_dt) SELECT carrier, origination_3, destination_3, service_dt + > FROM d_invoice_trucks WHERE carrier = .vcarrier + > AND origination_3 = .vorigin_3 + > AND destination_3 = .vdestin_3 + > AND service_dt = .vscedt + > AND COUNT > 1 > > FETCH c1 INTO vcarrier INDICATOR iv1, vorigin_3 INDICATOR iv3, vdestin_3 > INDICATOR iv4, vscedt INDICATOR iv5 > > ENDWHILE > DROP CURSOR c1 > > ===== Albert Berry Management Consultant RR2 - 1252 Ponderosa Drive Sparwood BC, V0B 2G2 Canada (250) 425-5806 (250) 425-7259 (708) 575-3952 (fax) [EMAIL PROTECTED]
