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]
