A tough query: Filtering records based upon previous result set records
By John Adolph Palinski
A very difficult query that I have encountered arises with the need to evaluate values from different records in a result set.
A Select statement uses the Where clause to filter values.
Conditions consisting of two arguments separated by an evaluation operator return a Boolean value (true or false).
If all of the conditional Boolean values are true, the record is accepted and placed in the result set.
Argument values typically come from the most recent fetched record or have been entered by the user.
When a previous record determines the argument value the developer is really in a quandary.
How do you bring a previously fetched value into the current evaluation?
In a typical statement, once a record is rejected or accepted, the record is discarded and the values are no longer available.
The developer must then look at some sophisticated program that has looping structures and variables.
This can greatly increase the difficult in producing the information.
Fortunately, Oracle’s Lag analytic function can help.
To illustrate, electric utilities often estimate monthly usage.
However, the utility does want to estimate all readings.
A typical utility wants to read the meter at least once in a three-month period.
Thus, you have been asked to create a Select statement that displays the customers that have had estimated readings for the past three months.
Can you write this Select statement?
Listing 1 displays a set of customer reading records.
Notice that customers 100 and 300 have had two actual readings in the past three months while customer 100 has had three estimated readings in this period.
To help the utility, it is necessary to write a Select statement that identifies customer 200 as failing to have an actual reading in one of the past three months.
Listing 1 – Electric_readings records.
select * from electric_readings;
ACCOUNT_NUMBER READING_D E READING
-------------- --------- - ----------
100 01-NOV-03 E 300
100 01-DEC-03 A 450
100 01-JAN-04 A 500
200 01-NOV-03 E 390
200 01-DEC-03 E 450
200 01-JAN-04 E 570
300 01-NOV-03 A 490
300 01-DEC-03 A 650
300 01-JAN-04 E 870
9 rows selected.
The best way to produce this information is to use the Lag function. This function allows you to retain previous values for use in the current record. Listing 2 illustrates this function. Notice lines 4, 6, 8, and 10. An in-line view used the Lag function calculate the account number and estimate/actual indicator for the previous two periods. The second Lag function parameter determines the record offset. The record offset is which previous record. The function on line 4 has a value of 1. This returns the est_act_indicator for the previous record. The function on line 8 returns the est_act_indicator for the second previous record. The remaining Lag functions determine the previous two account numbers.
The in-line view computes the current record’s account number and est_act_indicator, as well as the same values for the previous two records.
Once this result set is available, it is simple to discard customer records that do not match the criteria of having three estimated readings in a row.
The filtering is performed by the Where clause beginning on line 14.
The first two conditions on lines 14-15 ensure the computed account numbers and est_act_indicator values are for the same customer.
If they are not, then the record is discarded.
The last three conditions evaluate the est_act_indicator.
The indicator for the current record and the previous three must equal ‘E’.
The result set for this Select statement returns customer 200 for the month of January, 2004.
Look at Listing 1 and determine whether it is the correct answer.
SQL> select account_number, reading
2 from
3 (select account_number, est_act_indicator, reading,
4 lag(est_act_indicator, 1)
5 over (order by account_number, reading_date) one_reading_old_indicator,
6 lag(account_number, 1)
7 over (order by account_number, reading_date) one_reading_old_account,
8 lag(est_act_indicator, 2)
9 over (order by account_number, reading_date)
10 two_readings_old_indicator,
11 lag(account_number, 2)
12 over (order by account_number, reading_date) two_reading_old_account
13 from electric_readings)
14 where account_number = one_reading_old_account
15 and account_number = two_reading_old_account
16 and est_act_indicator = 'E'
17 and one_reading_old_indicator = 'E'
18 and two_readings_old_indicator != 'E';
ACCOUNT_NUMBER READING
-------------- ----------
200 570
SQL>
I hope that you can see how valuable the Lag function is. The Lag function is one of the Oracle windowing functions. It has two parameters: the expression and the record offset. It also requires the Over clause. This clause determines the size of the window. It also determines how the window records are ordered. In the case of our example, the window consisted of the entire set of Electric_readings records. The window set was ordered by account_number and the reading_date.
You can read more about this and other analytical functions in my book “The Oracle SQL and PL/SQL Handbook:”, Addison Wesley, 2002.
About the Author
John Adolph Palinski is an Oracle Certified Professional developer.
He has been adjunct faculty at various institutions.
He is the author of: “Oracle9i Developer: Developing Web Applications with Forms Builder”, Course Technology, 2003; “Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analyst”, Addison Wesley, 2003; and the “Oracle Database Construction Kit”, Que, 1997.
He has also written numerous Oracle articles.
He provides various pre-developed and custom Oracle training courses through his company Realistic Software Training.
He is always on the search for interesting topics to write upon.
He can be contacted at
www.oracle-trainer.com
