Thanks Al. Sami loves those duh's, so she won't mind. I'm going to get around to understanding all of this. I promise...
> -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Albert Berry > Sent: Wednesday, February 16, 2005 12:46 PM > To: RBG7-L Mailing List > Subject: [RBG7-L] - Re: Auditing Records > > Claudine, another DUH for Sami. Corrections as below. > > CREATE INDEX DupFilter ON d_inbox (invoice_id,inbox_state) > > --If the invoice column is not a primary key in d_invoice_trucks, then > CREATE INDEX DupFiler2 ON d_invoice_trucks (invoice_id) > > --then modify the view to link on the invoice_id. > > CREATE VIEW vwDupCheck + > (carrier,origination_3,destination_3,service_dt) AS + > SELECT carrier,origination_3,destination_3,service_dt + > FROM d_invoice_trucks + > WHERE invoice_id IN + > (SELECT invoice_id FROM d_inbox + > WHERE inbox_state IN (2,4) ) + > GROUP BY t1.carrier,t1.origination_3,t1.destination_3,t1.service_dt + > HAVING COUNT (*) > 1 > > --- Claudine Robbins <[EMAIL PROTECTED]> wrote: > > > 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] > > > > > > > ===== > 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]
