Thanks Al. This is what I was looking for and soooo slick and easy! Guess I'll have to try the indices too. I don't understand the second one though. Maybe it's a typo but inbox_state is in d_inbox table. Or am I missing something altogether? Also, I thought I saw someone else creating indices, then dropping them on the fly. How would you do that?
> -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Albert Berry > Sent: Wednesday, February 16, 2005 11:30 AM > To: RBG7-L Mailing List > Subject: [RBG7-L] - Re: Auditing Records > > 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]
