Problems creating a materialized view in Oracle-CodePudding

I want to create a MV that fixes some columns in the join clause to the current date. So I have the following:

CREATE MATERIALIZED VIEW MV_FLYING_INFO
BUILD IMMEDIATE 
REFRESH COMPLETE START WITH (SYSDATE) NEXT (TRUNC(SYSDATE)   1)   2 / 24
ON DEMAND
DISABLE QUERY REWRITE AS
SELECT * FROM FLYING_INFO fi, DAYS d, MONTHS_YEARS my WHERE 
fi.DAYS = d.ID AND d.MONTHS_YEARS = my.ID 
AND my.MONTH_NUM = SELECT EXTRACT(MONTH FROM SYSDATE) 
     FROM DUAL 
     AND 
     my.YEAR_NUM = SELECT 
    EXTRACT(YEAR FROM SYSDATE) 
     FROM DUAL
     AND 
     d.DAY_NUM = SELECT 
    EXTRACT(DAY FROM SYSDATE)
     FROM DUAL;

And the tables that reference it

CREATE TABLE AIRCRAFT(
    id INT,
    model CHAR(255),
    manufacturer CHAR(255),
    PRIMARY KEY (id)
);

CREATE TABLE MONTHS_YEARS(
    id INT,
    month_num INT CHECK(month_num BETWEEN 1 AND 12),
    year_num INT CHECK(year_num BETWEEN 1903 AND 2022),
    PRIMARY KEY (id)
);


CREATE TABLE DAYS(
    id INT,
    day_num INT CHECK(day_num BETWEEN 1 AND 31),
    months_years INT,
    
    PRIMARY KEY (id),
    FOREIGN KEY (months_years) REFERENCES MONTHS_YEARS(id)  
);


CREATE TABLE FLYING_INFO(
    id INT,
    FH FLOAT,
    TOS FLOAT,
    aircraft INT,
    days INT,
    
    PRIMARY KEY (id),
    FOREIGN KEY (aircraft) REFERENCES AIRCRAFT(id),
    FOREIGN KEY (days) REFERENCES DAYS(id)
);

Here I have 2 problems:

First of all, I don't know how to avoid the error SQL Error [957] [42000]: ORA-00957: nombre de columna duplicado
Secondly, fixing the year, day and month with EXTRACT from SYSDATE is also giving problems (SQL Error [936] [42000]: ORA-00936: falta una expresión)

CodePudding user response：

As astentx has said, a little cleanup and you're good to go

SQL> CREATE TABLE AIRCRAFT(
  2      id INT,
  3      model CHAR(255),
  4      manufacturer CHAR(255),
  5      PRIMARY KEY (id)
  6  );

Table created.

SQL>
SQL> CREATE TABLE MONTHS_YEARS(
  2      id INT,
  3      month_num INT CHECK(month_num BETWEEN 1 AND 12),
  4      year_num INT CHECK(year_num BETWEEN 1903 AND 2022),
  5      PRIMARY KEY (id)
  6  );

Table created.

SQL>
SQL>
SQL> CREATE TABLE DAYS(
  2      id INT,
  3      day_num INT CHECK(day_num BETWEEN 1 AND 31),
  4      months_years INT,
  5      PRIMARY KEY (id),
  6      FOREIGN KEY (months_years) REFERENCES MONTHS_YEARS(id)
  7  );

Table created.

SQL>
SQL>
SQL> CREATE TABLE FLYING_INFO(
  2      id INT,
  3      FH FLOAT,
  4      TOS FLOAT,
  5      aircraft INT,
  6      days INT,
  7      PRIMARY KEY (id),
  8      FOREIGN KEY (aircraft) REFERENCES AIRCRAFT(id),
  9      FOREIGN KEY (days) REFERENCES DAYS(id)
 10  );

Table created.

SQL>
SQL>
SQL> CREATE MATERIALIZED VIEW MV_FLYING_INFO
  2  BUILD IMMEDIATE
  3  REFRESH COMPLETE START WITH (SYSDATE) NEXT (TRUNC(SYSDATE)   1)   2 / 24
  4  ON DEMAND
  5  DISABLE QUERY REWRITE AS
  6  SELECT
  7    my.ID mid
  8  , my.MONTH_NUM
  9  , my.YEAR_NUM
 10  , d.ID did
 11  , d.DAY_NUM
 12  , d.MONTHS_YEARS
 13  , fi.ID fod
 14  , fi.FH
 15  , fi.TOS
 16  , fi.AIRCRAFT
 17  , fi.DAYS
 18  FROM FLYING_INFO fi, DAYS d, MONTHS_YEARS my
 19  WHERE fi.DAYS = d.ID AND d.MONTHS_YEARS = my.ID
 20  AND my.MONTH_NUM = EXTRACT(MONTH FROM SYSDATE)
 21  AND      my.YEAR_NUM = EXTRACT(YEAR FROM SYSDATE)
 22  AND     d.DAY_NUM = EXTRACT(DAY FROM SYSDATE);

Materialized view created.