Re: help - pl/sql procedure for data summary

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/04/20
Message-ID: <956259413.8937.0.pluto.d4ee154e_at_news.demon.nl>#1/1

lifter <pummellerNOpuSPAM_at_netscape.net.invalid> schreef in berichtnieuws 0048b28c.00e39d1b_at_usw-ex0109-068.remarq.com...
> 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
>

Sometimes you would have wished Oracle never introduced PL/SQL. Here's why: because it withhelds people from learning SQL.

Personally I don't see why this can not be done by one (1) single sql-statement albeit using inline views
First to determine the excess:

select t1.account#

     , duration
     , decode(sign(duration - avg_duration), 1, duration _ avg_duration
                   , 0) excess -- and no pl/sql function needed

from table1 t1, table2 t2, table3 t3
where t2.account# = t1.account#

   and t3.group = t2.group

secondly the summary
select t3.group

     , count(t1.*)
     , sum(decode(sign(duration - avg_duration), 1, duration _ avg_duration
                   , 0)) excess -- and no pl/sql function needed

from table1 t1, table2 t2, table3 t3
where t2.account# = t1.account#

   and t3.group = t2.group
group by t3.group

BTW you will run into trouble probably because group is a reserved word. You probably won't get syntax errors, but ambiguous results instead.

In theory this should be all you need for this report.

Hth,

Sybrand Bakker, Oracle DBA Received on Thu Apr 20 2000 - 00:00:00 CEST