help - pl/sql procedure for data summary

[Quoted] I'm new to Oracle and need help with logic in a PL/SQL procedure. Goal is to perform a calcuation on detail records and then output a summary file. Read on if you can help!

Table1 (transaction detail): account#, date, time, duration [10 million+ records]
Table2 (customer info): account#, group [500+ records] Table3 (group info): group, avg_duration [10 records]

For each transaction I need to calculate the number of seconds that Table1.duration exceeds Table3.avg_duration. I am doing this with a function:
calc_excess(avg_duration, duration) If duration <= avg_duration, excess=0.
I have been using a cursor to link all three tables, and the fuction is part of my SELECT statement in the cursor declaration.

Here's where I need help: I need to take the results of the previous cursor and end up with the following flat file or table:
group, total_transaction_count, sum_excess_seconds This will have one record per group. How can I pass the result set of the first cursor into a second cursor and end up with the necessary summary information?

Thanks in advance!

• Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping. Smart is Beautiful