Oracle PL/SQL for DBAs

Identifying Potential Repeats

Now for the slightly difficult part—finding repeat orders. I’ve decided to do this in two distinct operations. The first operation determines if the order is a potential outage by matching its order type and date with the first order type in a criterion. The second operation decides if the order is a genuine repeat by matching its location and order type with the repeat-order type of a potential outage.

I’ll explain further with some example data. Consider the following order criterion record:

    START_DAT FIRST_TYPE_ID REPEAT_TYPE_ID
    --------- ------------- --------------
    19-APR-05           801             87

This record states that an order on or after April 19, 2005, with an order type of 334 that is followed within 30 days by an order of type 87 at the same location is considered a repeat order.

Now consider the following three orders.

    ORDER_NUMBER ORDER_DAT   TYPE_ID LOCATION_ID
    ------------ --------- ---------- ----------
            1016 19-APR-05        801        343
            1863 20-APR-05         87        343
            2228 21-APR-05         87        343

When processed by my function order, 1016 would become a potential repeat order for order type 801 and location 343. Any subsequent order of type 87 within 30 days at location 343 would be considered a genuine repeat order. Thus, orders 1863 and 2228 would be counted as genuine repeats by my function.

To find genuine repeat orders, I need to discover potential repeats first. For the sake of clean code, I’ll implement the “potential repeats” logic as a sub-function named load_potential_repeat. ...