Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: HELP! How to check missing records
No you can not get that kind of info from SQL
Here is a stored procedure that will do the job:
Assumptions I made:
1) Driver-ID, Vehicle-ID Trip_No makes up a UNIQUE KEY
2) Trip number is supposed to start from 1
**** What you need to do ****
Replace ALL references to table and fields with the actual names of the
table/fields:
Table: Drivers
Fields:
did = Driver-ID
vid = Vehicle-ID
trip_no = Trip_No
This procedure only runs against this one table of yours, compile in sql+, then run it by typing: exec find_missing_trips
HTH
Robert Chin
CREATE OR REPLACE
PROCEDURE find_missing_trips
IS
CURSOR driver_cur
IS SELECT max(did) did, max(vid) vid FROM drivers GROUP BY did, vid;
PROCEDURE whats_missing (v_did IN drivers.did%TYPE, v_vid IN
drivers.vid%TYPE)
IS
CURSOR whats_missing_cur
IS SELECT did, vid, trip_id FROM drivers where did = v_did and vid = v_vid ORDER BY trip_id;TYPE trip_id_tabletype IS TABLE OF whats_missing_cur%ROWTYPE INDEX BY BINARY_INTEGER;
BEGIN
FOR rec_whats_missing_cur IN whats_missing_cur
LOOP
trip_id_tab(rec_whats_missing_cur.trip_id) := rec_whats_missing_cur;
END LOOP;
dbms_output.put_line('Driver/Car '||trip_id_tab(trip_id_tab.FIRST).did ||'/' ||trip_id_tab(trip_id_tab.FIRST).vid ||' is missing trip(s):'); dbms_output.put_line('--------------------------------------');FOR i IN 1 .. trip_id_tab.LAST
IF trip_id_tab.EXISTS(i) THEN NULL; ELSE dbms_output.put_line(i); END IF;
whats_missing(rec_driver_cur.did, rec_driver_cur.vid); dbms_output.put_line(' ');
"Ho" <kowho_at_singnet.com.sg> wrote in message news:9qt69u$bcm$1_at_clematis.singnet.com.sg...
> Hi , > I need to check the missing records (trip no.) of the following table: > > Driver-ID Vehicle-ID Trip No. > > ABC 1234 1 > 3 > 5 > 6 > > BCD 2345 1 > 2 > 1 5 > .... > > > For driver ABC mssing record are trip no 2 & 4, for driver BCD the > missing records are 3 & 4. > > How to achieve this. Can it be done through SQL ? > > Many thanks for your help in advance. > > >Received on Sun Oct 21 2001 - 12:27:54 CDT