ansaurus

Question

Answer 1

A:

To populate a table you need to INSERT.

INSERT INTO TargetTable
  (
    str_week_day,
    end_week_day,
    age_id,
    eq_type
  )

SELECT
   start_date,
   end_date,
   age_id,
   eq_type
FROM
   SourceTable

JonH 2010-07-13 12:04:32

sorry about the select and the insert confusion .. i have edited my reuirements

ramkumar 2010-07-13 16:33:21

Answer 2

A:

Have you considered creating a view instead of a table? The CREATE VIEW statement is just a query, and the same query you'd use to populate the table would do to define the view. The view won't become out of date.

Brian Hooper 2010-07-13 14:24:17

Answer 3

A:

As @JonH stated, you need to use an insert to populate a table. Making a number of assumptions, your query might look like this:

insert into Target_Table
       (str_week_day, end_week_day, age_id, usage_ratio eq_type)
select str_week_day,
       week_start+6 as end_week_day
       a.aid as age_id,
       a.cnt/m.cnt as usage_ratio,
       (case when a.m>a.r then 'M' else 'R'end) as eq_type
from
      (select aid,
              sum(case when eq_type='M'then 1 else 0 end) as "M",
              sum(case when eq_type='R'then 1 else 0 end) as "R" ,
              count(*) as cnt,
              trunc(start_date,"D") as str_week_day
       from mfrp1 
       group by aid, 
                trunc(start_date,"D")) a,
      (select count(*) as cnt from mfrp1) m;

However, as @Brian Hooper stated, you'd be much better off with a view. As he mentioned, the view would be always be consistent with the source data, as it is simply a stored query that runs against that data on demand. As a view is accessed in exactly the same manner as a table, they are mostly indistinguishable to a user (be it a person or an application).

create or replace view Target_Table as
select str_week_day,
       week_start+6 as end_week_day
       a.aid as age_id,
       a.cnt/m.cnt as usage_ratio,
       (case when a.m>a.r then 'M' else 'R'end) as eq_type
from
      (select aid,
              sum(case when eq_type='M'then 1 else 0 end) as "M",
              sum(case when eq_type='R'then 1 else 0 end) as "R" ,
              count(*) as cnt,
              trunc(start_date,"D") as str_week_day
       from mfrp1 
       group by aid, 
                trunc(start_date,"D")) a,
      (select count(*) as cnt from mfrp1) m;

If this is too slow, you might consider a materialized view, which will do a lot of the work of keeping the aggregated data up-to-date, but stores the data separately to speed access. In most cases, this isn't necessary though.

Allan 2010-07-13 15:20:33

ansaurus

tags:

views:

answers:

populating a aggregate table

target table

source table

related questions