CODEWITHSUNDEEP
oracle18c
Daniel Benedí
Daniel Benedí

Reputation: 79

Oracle 18c - ERROR CREATING MATERIALIZED VIEW - ORA-00933

I tried to create a materialized view on a oracle 18c and I got a ORA-00933.

CREATE MATERIALIZED VIEW Top10Cancelacion
BUILD IMMEDIATE
REFRESH ON DEMAND 
AS SELECT idAeropuerto 
FROM aeropuerto 
ORDER BY (  SELECT count(*)
            FROM vuelo v JOIN incidencia i
                ON v.idVuelo = i.idVuelo
            WHERE (idAeropuerto = v.origen OR
                    idAeropuerto = v.destino) AND
                i.tipo = 'Cancelado'
        )/( SELECT count(*) FROM vuelo )
    DESC
FETCH FIRST 10 ROWS ONLY;

I have tried deleting the FETCH FIRST line and the ORDER BY, but I have the same error. I have tried to executing the query and it works without problem. Despite that, this is my database structure of the important parts:

CREATE TABLE Aeropuerto(
        idAeropuerto    VARCHAR2(4),
        nombre          VARCHAR2(100),
        ciudad          VARCHAR2(100),
        estado          VARCHAR2(100),
        pais            VARCHAR2(100),
        latitud         DECIMAL(11,8),
        longitud        DECIMAL(11,8),
        PRIMARY KEY (idAeropuerto)
);
CREATE TABLE Vuelo(
    idVuelo         NUMBER,
    origen          VARCHAR2(4),
    destino         VARCHAR2(4),
    avion           VARCHAR2(10),
    tiempoOrigen    TIMESTAMP,
    tiempoDestino   TIMESTAMP,
    duracion        NUMBER,
    distancia       NUMBER,
    PRIMARY KEY (idVuelo),
    FOREIGN KEY (origen) REFERENCES Aeropuerto(idAeropuerto) ON DELETE CASCADE,
    FOREIGN KEY (destino) REFERENCES Aeropuerto(idAeropuerto) ON DELETE CASCADE,
    FOREIGN KEY (avion) REFERENCES Avion(idAvion) ON DELETE CASCADE,
    CONSTRAINT distinct_places CHECK (origen != destino)
);
CREATE TABLE Incidencia(
    idVuelo NUMBER,
    tipo    VARCHAR2(100),
    detalles VARCHAR2(100),
    PRIMARY KEY (idVuelo, tipo),
    FOREIGN KEY (idVuelo) REFERENCES Vuelo(idVuelo) ON DELETE CASCADE
);

Is there something I haven't seen? Thank you in advance

Upvotes: 0

Views: 284

Answers (1)

pmdba
pmdba

Reputation: 7033

Looks like an old syntax parser bug. Wrap the query in parenthesis, like this:

SQL> CREATE MATERIALIZED VIEW Top10Cancelacion
BUILD IMMEDIATE
REFRESH ON DEMAND 
AS (SELECT idAeropuerto 
FROM aeropuerto 
ORDER BY (  SELECT count(*)
            FROM vuelo v JOIN incidencia i
                ON v.idVuelo = i.idVuelo
            WHERE (idAeropuerto = v.origen OR
                    idAeropuerto = v.destino) AND
                i.tipo = 'Cancelado'
        )/( SELECT count(*) FROM vuelo )
    DESC
FETCH FIRST 10 ROWS ONLY);

Materialized view created.

Upvotes: 1

Related Questions