Your error says the following: ORA-12015: cannot create a fast refresh materialized view from a complex query That means that you are trying to create a MV using a join and not just a subset of a single table. Use "REFRESH COMPLETE" instead of "REFRESH FAST" and you'll be OK. On 01/23/2004 09:56:59 AM, Ramón Estevez wrote: > Hi list, > I want to create a MV between those two tables and I get an error, > here is the case: > > I checked metalink and the documentation either is not clear or I > don't understand, choose that one, but just are two tables with a > single join. > > > TIA > > > SQL> DESC ORIGINALES > Name Null? Type > ------------------------------- -------- ---- > GRUPO NOT NULL NUMBER(2) > COMPANIA NOT NULL NUMBER(2) > NUMERO_ORIGINAL NOT NULL VARCHAR2(20) > NRO_DESCRIPCION NUMBER(4) > LINEA NUMBER(2) > FECHA_CREACION NOT NULL DATE > PRECIO_VENTA NUMBER(8,2) > ESTATUS_PEDIR VARCHAR2(1) > > SQL> DESC DESCRIPCIONES > Name Null? Type > ------------------------------- -------- ---- > NRO_DESCRIPCION NOT NULL NUMBER(4) > DESCRIPCION NOT NULL VARCHAR2(30) > > SQL> CREATE MATERIALIZED VIEW LOG ON ORIGINALES > 2 WITH SEQUENCE, ROWID > 3 (GRUPO, COMPANIA, NUMERO_ORIGINAL, NRO_DESCRIPCION, LINEA, > PRECIO_VENTA) > 4 INCLUDING NEW VALUES; > > Snapshot log created. > > SQL> CREATE MATERIALIZED VIEW LOG ON DESCRIPCIONES > 2 WITH SEQUENCE, PRIMARY KEY > 3 (DESCRIPCION) > 4 INCLUDING NEW VALUES; > > Snapshot log created. > > SQL> CREATE MATERIALIZED VIEW MV_ORIGINALES > 2 BUILD IMMEDIATE > 3 REFRESH FAST ON COMMIT > 4 ENABLE QUERY REWRITE > 5 AS > 6 SELECT O.GRUPO, O.COMPANIA, O.NUMERO_ORIGINAL, > 7 D.DESCRIPCION, O.LINEA, O.PRECIO_VENTA > 8 FROM ORIGINALES O, DESCRIPCIONES D > 9 WHERE O.NRO_DESCRIPCION = D.NRO_DESCRIPCION > 10 / > FROM ORIGINALES O, DESCRIPCIONES D > * > ERROR at line 8: > ORA-12054: cannot set the ON COMMIT refresh attribute for the > materialized view > > ----- > > SQL> ED > Wrote file afiedt.buf > > 1 CREATE MATERIALIZED VIEW MV_ORIGINALES > 2 BUILD IMMEDIATE > 3 REFRESH FAST ON DEMAND > 4 ENABLE QUERY REWRITE > 5 AS > 6 SELECT O.GRUPO, O.COMPANIA, O. > NUMERO_ORIGINAL, > 7 D.DESCRIPCION, O.LINEA, O.PRECIO_VENTA > 8 FROM ORIGINALES O, DESCRIPCIONES D > 9* WHERE O.NRO_DESCRIPCION = D.NRO_DESCRIPCION > SQL> / > FROM ORIGINALES O, DESCRIPCIONES D > * > ERROR at line 8: > ORA-12015: cannot create a fast refresh materialized view from a > complex query > > > Ramon E. Estevez > restevez@xxxxxxxxxx > 809-535-8994 > > > > >