0

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!:)

aquawhale
  • 11
  • 3
  • Please, provide [minimal **reproducible** example](https://stackoverflow.com/help/minimal-reproducible-example). Visual compilation of the query is not what may give you a correct answer – astentx Jul 15 '22 at 10:50
  • Does your query run fine in isolation (ie, not as part of a mview)? – Connor McDonald Jul 18 '22 at 00:42
  • Minimal reproducible example with table inserts and populating table data: https://codeshare.io/8pBnxv – aquawhale Jul 18 '22 at 13:41
  • Yes!, The query runs fine in isolation. Error happens only if wrapping select statement with CREATE MATERIALIZED VIEW statement. I kinda suspecting it does not like OUTER APPLY, maybe I'm wrong. – aquawhale Jul 18 '22 at 13:45

1 Answers1

0

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.

aquawhale
  • 11
  • 3