OraFAQ Forum: SQL & PL/SQL » ORA-12015: cannot create a fast refresh materialized view from a complex query

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-12015: cannot create a fast refresh materialized view from a complex query (Oracle 11g (11.2.0.3))

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-12015: cannot create a fast refresh materialized view from a complex query [message #644641]

Wed, 11 November 2015 06:33

tony_soprano786
Messages: 5
Registered: November 2015

Junior Member

Hi

I'm having trouble creating a fast refreshing materialized view from:

select 
case when adj_trading_date IS NOT NULL
then adj_trading_date    
else eod.trading_date
end
from reconciliation_MUY.Emis_Outlet_Detail eod
LEFT OUTER JOIN reconciliation_MUY.emis_trading_date_adj adj on adj.outletindx = eod.outletindx;

Each time I try, I get: 'ORA-12015: cannot create a fast refresh materialized view from a complex query'.

The query itself seems quite simple - I'm not sure why it's deemed "complex"? How could I re-write it so that it isn't complex?

Thanks.

Report message to a moderator

Re: ORA-12015: cannot create a fast refresh materialized view from a complex query [message #644642 is a reply to message #644641]

Wed, 11 November 2015 07:20

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

You have to use Oracle native join syntax, not ANSI syntax. This is (perhaps) the last remaining bug in Oracle's implementation of ANSI joins.

Report message to a moderator

Re: ORA-12015: cannot create a fast refresh materialized view from a complex query [message #644701 is a reply to message #644642]

Fri, 13 November 2015 15:35

Bill B
Messages: 1971
Registered: December 2004

Senior Member

select
case when adj_trading_date IS NOT NULL
then adj_trading_date
else eod.trading_date
end
from reconciliation_MUY.Emis_Outlet_Detail eod,reconciliation_MUY.emis_trading_date_adj adj
where adj.outletindx = eod.outletindx(+);

Report message to a moderator

Previous Topic:	Determine Incomplete Orders
Next Topic:	arraysize and PL/SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 21:10:44 CDT 2024