Is it possible? Whenever I try to create the simplest of materialized views that contain more than one referance to a table it gives me a 'ORA-12015: cannot create a fast refresh snapshot from a complex query' error. I'm running Oracle 8i and Windows 2000. Originally I tried to do this on one of my real tables but I've created a test table and matview that gives the same error that looks like this:

create table testone
(a varchar(3) not null,
b varchar(3) null,
c varchar(3) null,
d varchar(3) null);

insert into testone (a,b,c,d)
values (1,5,7,3);

insert into testone (a,b,c,d)
values (2,9,6,4);

create materialized view log on testone;

create materialized view test_mv
parallel
build immediate
refresh fast on demand
as
select
a.rowid as a_rid, b.rowid as b_rid,
a.a, a.b, a.c, a.d,
b.a as z, b.b as y, b.c as x, b.d as w
from testone a, testone b
where a.a = b.a;

Also I didn't see any restrictions preventing me from using multiple aliases of one table in a fast refresh materialized view but when I try to use them it's giving me the complex query error again.

I'm using this

Code Sample
create materialized view test_mv
parallel
build immediate
refresh fast on demand
as
select
a.rowid as a_rid, b.rowid as b_rid,
a.a, a.b, a.c, a.d,
b.a as z, b.b as y, b.c as x, b.d as w
from testone a, testone b
where a.a = b.a



On a related note: I can also create a fast refresh matview on two joined tables (as I said earlier) and I can also create a fast refresh on a table using a case statement; but, I can't create a fast refresh matview on two tables and use a case statement. (I can't do them together). I get that complex query error. . . again. Is it possible to do in 8i? Would any of this stuff be possible in 9i? Again, I can't use primary keys. Anyone? I would really appreciate any help. Thanks again.

If you want to take a look, this works:

CREATE MATERIALIZED VIEW GD_TEST_FMV
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
with rowid
AS
select case
when testtable.colone = 1 then testtable.coltwo
else null
end as testcol
FROM testtable


but for some strange reason it dosen't work if I omit the 'with rowid' statement and put a testtable.rowid in the select statement. It gives me an error 'ORA-12014: 'testtable' does not contain a primary key constraint'. And if I put both I get 'ORA-00904: invalid column name' on testtable in my from statement.

and so does

create materialized view test_mv
parallel
build immediate
refresh fast on demand
as
select
a.rowid as a_rid, b.rowid as b_rid,
a.a, a.b, a.c, a.d,
b.a as z, b.b as y, b.c as x, b.d as w
from testone a, testtwo b
where a.a = b.a


but this dosen't :

CREATE MATERIALIZED VIEW GD_TEST_FMV
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
with rowid
AS
select case
when testtable.colone = 1 then testtable.coltwo
else null
end as testcol
FROM testtable, othertesttable
WHERE testtable.colone = othertesttable.colone