RE: [oracle-l] MVs

From: Ramón Estevez <REstevez_at_blh.com.do>
Date: Wed, 28 Jan 2004 08:47:09 -0400
Message-ID: <80205D3A07BB114888ECFA425824BACE2AEF03@bdiblh02.bdiblh.apps>

Re submitting a previous issue, had problems with my email

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 =3D 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 =3D 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_at_blh.com.do
809-535-8994

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Jan 28 2004 - 06:47:09 CST