Using Table Functions
In this section, I’ll show the use of table functions in a real-world example based on a large cable company’s attempt to track repeat orders. Simply put, a repeat order occurs when a technician is dispatched to a location more than once within a 30-day period to perform the same type of work or to repair the original work. A repeat visit doesn’t always have a negative connotation—it could result from an installation occurring within 30 days of a pre-sales call.
Locations are identified by preassigned ID values. These can map to anything from a specific neighborhood cable outlet to a house or a large shopping mall. The type of work is identified using order type IDs. For example, type 1 might be “Cable Installation,” and type 2 might be “Cable Upgrade.”
The company is divided into several regions, each with its own set of location codes, order types, and repeat-order criteria. These criteria define sets of two order types that must occur at the same location within 30 days of each other to be considered a repeat order. The two order types may or may not be the same; for example, a “Cable Installation” followed by a subsequent “Cable Repair” might be deemed a repeat order in the same way that two “Cable Installations” would.
The criteria are held in this table:
SQL> DESC repeat_order_criteria
Name Null? Type
----------------------------------------- -------- ------
REGION_ID NUMBER
START_DATE DATE
FIRST_TYPE_ID NUMBER
REPEAT_TYPE_ID NUMBER
The table holds each region’s ...
Get Oracle PL/SQL for DBAs now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.