CODEWITHSUNDEEP
sqloracle-databaseora-00907outer-apply
aquawhale
aquawhale

Reputation: 11

Oracle Create Materialized View With Outer Apply Throws "missing right parenthesis" Error

I'm trying to create materialized view in Oracle DB (version: 19c), but I get the following error:

ORA-00907: missing right parenthesis

I'm kinda suspecting maybe outer apply has something to do with it? Maybe it does not allow it? The query is: (for each day between '2022-02-10' and NOW, it fetches last created CURRRENCY_RATES record on that day if it exists, if it does not exist then for that day it fetches previous created record on last previous day -> outer apply)

CREATE MATERIALIZED VIEW "MV_LAST_CREATED_RATE_PER_DAY" 
("DAY_ID", "CURRRENCY_RATE_ID", "FROM_CURRRENCY_ID", "TO_CURRRENCY_ID", "RATE", "VALIDITY_DATE", "CREATE_DATE")
  BUILD IMMEDIATE
  REFRESH FORCE ON DEMAND
  AS   
  WITH DAYS AS (
      select TO_NUMBER (TO_CHAR (date'2022-02-10' + level - 1, 'yyyymmdd')) DAY_ID
      from   dual
      connect by level <= (
          sysdate - date'2022-02-10' + 1
      )
  ),
  LAST_CREATED_RATE_ON_DAY AS (
      SELECT *
         FROM (
            SELECT TO_NUMBER (TO_CHAR (CREATE_DATE, 'yyyymmdd')) as DAY_ID,
                   RATES.*,
                   RANK ()
                      OVER (
                         PARTITION BY TO_NUMBER (TO_CHAR (CREATE_DATE, 'yyyymmdd')),
                                 FROM_CURRRENCY_ID,
                                 TO_CURRRENCY_ID
                          ORDER BY CURRRENCY_RATE_ID DESC)
                                                      AS RNK
            FROM CURRRENCY_RATES RATES)
      WHERE RNK = 1
   )
   SELECT d.DAY_ID,
          lcrod.CURRRENCY_RATE_ID,
          lcrod.FROM_CURRRENCY_ID,
          lcrod.TO_CURRRENCY_ID,
          lcrod.RATE,
          lcrod.VALIDITY_DATE,
       lcrod.CREATE_DATE
   FROM DAYS d
   OUTER APPLY (
        SELECT * 
        FROM LAST_CREATED_RATE_ON_DAY lcrod
        WHERE lcrod.DAY_ID <= d.DAY_ID
        ORDER BY lcrod.DAY_ID DESC
        FETCH NEXT 1 ROWS ONLY
   ) lcrod;

Will be grateful for any help!:)

Upvotes: 0

Views: 179

Answers (1)

aquawhale
aquawhale

Reputation: 11

I could not get the query to run withing CREATE MATERIALIZED VIEW statement.

So I split materialized view creation into view and materialized view. So I created view with above WITH clause, then created materialized view with selected data from created view.

Upvotes: 0

Related Questions