SQL & PL/SQL

Hello,

I'm working in 10g environment.

I have a table prices in which I have the prices of products by periods of validity (from period_1 to period_2).

create table prices
(price_id number(6) primary key,
product_id number(6),
price number(10,2),
period_1 date,
period_2 date);

Since the prices can change, for one period for one product_id I can have several rows in my table so that I have to take the row with the highest price_id.

In consequence, this is my query

select max(price_id) as valid_price_id,product_id
from prices
where trunc(sysdate) between period_1 and period_2
group by product_id;

I have to execute this query many times so I wanted to create a materialized view.

I started to create

create function My_Sysdate return date
is
begin
return trunc(sysdate);
end;
/

then I create the following materialized view

CREATE MATERIALIZED VIEW MY_DATE
REFRESH
START WITH trunc(sysdate+1)+ 1/(24*3600) NEXT TRUNC(SYSDATE + 1) + 1
AS
SELECT My_Sysdate AS VALUE FROM DUAL;

and finally

create materialized view my_price
refresh on commit complete
as
select max(price_id) as max_price_id,product_id
from prices, my_date
where value between period_1 and period_2
group by product_id;


But when I do this my view my_price is not refreshed after a new insert into prices.

What's the problem ?



-- Data for example
insert into prices values (1,1,3,to_date('01/01/2008','DD/MM/YYYY'),to_date('01/02/2008','DD/MM/YYYY'));

insert into prices values (2,1,4,to_date('02/02/2008','DD/MM/YYYY'),to_date('01/03/2008','DD/MM/YYYY'));

insert into prices values (3,1,2,to_date('02/03/2008','DD/MM/YYYY'),to_date('05/03/2008','DD/MM/YYYY'));

insert into prices values (4,1,5,to_date('06/03/2008','DD/MM/YYYY'),to_date('15/03/2008','DD/MM/YYYY'));

insert into prices values (5,1,2.3,to_date('02/03/2008','DD/MM/YYYY'),to_date('05/03/2008','DD/MM/YYYY'));

commit;

Message was edited by:
Zabo