Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page