J
At 12:06 PM 2/16/2005, you 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
