Help - to design procedure Options

[ora99]

I have a table (refreshed weekly) with about 15million records and over 600 columns, table contains customer aggregated information and “BILLING_ACCOUNT” is the primary key in the table. There are two types of data in the table:

1. with continuous data like “TOTAL_BILL” , “TOTAL_USAGE”...
2. with categorical values like “CONNECTION_TYPE”, “OPTION_PLAN” now this may have either single character values like (‘A’,’B’,’C’), (‘1’,’2’,’3’) or like (‘option_1_single’, ‘option_2_single’, ‘option_1_double’, ‘option_2_double’) and new categories may be added or removed during table refresh.

Problem:
I have to generate report where I have to compute mean,max,min and sum for 1st type of data and frequency distribution for 2nd kind of data for about 100 fields for whole base (not at individual customer level ). Please note that list of 100 fields could change every week depending on business need.
Could I design a generic procedure to do this !?!

[burleson]

>> Could I design a generic procedure to do this !?!

Yes.


****************************

>> compute mean,max,min and sum for 1st type of data

I would use scalar subqueries to compute the min. max values:

http://www.dba-oracle.com/art_9i_scalar_subq.htm


*****************************
>> frequency distribution for 2nd kind of data for about 100 fields for whole base

I would write this in PL/SQL using bulk collect and forall

For syntax examples, see the code download in this book:

http://www.rampant-books.com/book_0601_plsql_tune.htm