Max function limitation in Materialized View

[ronnie]

Hi,

I am trying to create a MV as

create materialized view MV_MAXPRICEDATE_STOCKPRICE
build immediate
refresh complete on commit
enable query rewrite
as
select company_id,exchange_id,count(price_date),COUNT(*),max(price_date)
as price_date
from stock_price
group by
company_id,exchange_id
/

But it gives me the following error.

ERROR at line 8:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

I know its because of the max function. If I use sum instead of max it works fine.

Why does Oracle have this limitation and is there any workaround to it because I dont want to refresh My MV manually.

That will be a pain in the ...... you know what :-)

Thanks

[Calvin_Qiu]

I have made a test, It worked fine as your situation.

Check page 120/618 of Data Warehousing Guide.