Try MetaLink doc 101705.1 Ramón Estevez <REstevez@xxxxxxxxxx> Sent by: oracle-l-bounce@xxxxxxxxxxxxx 01/23/2004 06:56 AM Please respond to oracle-l To: <oracle-l@xxxxxxxxxxxxx> cc: Subject: [oracle-l] MVs 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