Re: HELP! How to check missing records

From: Robert Chin <chinman_at_optonline.net>
Date: Sun, 21 Oct 2001 17:27:54 GMT
Message-ID: <uADA7.10660$wS2.1601526_at_news02.optonline.net>

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;
    trip_id_tab trip_id_tabletype;

    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
    LOOP

        IF trip_id_tab.EXISTS(i) THEN
           NULL;
        ELSE
           dbms_output.put_line(i);
        END IF;

    END LOOP;
    END;

---

---

BEGIN
    FOR rec_driver_cur IN driver_cur
    LOOP

        whats_missing(rec_driver_cur.did, rec_driver_cur.vid);
        dbms_output.put_line(' ');

    END LOOP;
END;
/

"Ho" <kowho_at_singnet.com.sg> wrote in message news:9qt69u$bcm$1_at_clematis.singnet.com.sg...

> Hi ,
[Quoted] > 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 - 19:27:54 CEST