I want to create an aggregated or summary level at database level.
As my raw data is huge and my raw tables are partitioned into different tables to store such huge amount of data.
I want to create several summary table/view which can aggregate data at different levels.
What is the best option to do this -
1. Creation of permanent table ('select into' or 'create table')
2. Creation of simple view with 'union all' and group by.
3. Creation of Materialized view (never used this before so not sure of its impacts)
Also I want this table/view to be updated every so often. How can we achieve this?
You don't want to sum values to a table. (Normaly)
You want to keep it 'live'. So you would just open the query.
If it is many different queries, then build a UNION query.
If all these methods still won't get what you need, the yes, you can sum data to a reporting table. Build the table once, format yr fields, then you macro would...
Run Append queries
sorry, I see this is a sybase question, so Im not sure about the 'macros'.
but For my odd totals, I have a report table with the fields formated specifically for Long, or Date, or single, etc.
I have a series of queries that sum and append to the table to build the report.
I have a macro that :
empties the report table
adds sum qry 1
adds sum qry 2