Materialized View is coming as invalid materialized view definition in Snowflake

Question

SQL compilation error: error line 0 at position -1 Invalid materialized view definition. More than one table referenced in the view definition

Materialized View is coming as invalid materialized view definition in Snowflake. What is the alternate approach if we want to query more than one table??

    create or replace procedure mv_test()
     returns string
     language javascript
     execute as caller
     as
     $$
    
         function log (msg) {
             snowflake.createStatement( { sqlText: `call do_log ( :col1, :col2 )`, binds:[ 'mv_test', msg ] } ).execute();
        }
    
        try
        {
            CALL DBMS_MVIEW.REFRESH('mv1');
        }
        catch (err)
        { 
          console.error ('Error :,',(+ err.code+' '+ err.message));
              return}   }

$$;

---

The Oracle SP script for the above is:

  create or replace PROCEDURE test_sp_orcl As
    BEGIN
    DBMS_MVIEW.REFRESH('mv1');
    END test_sp_orcl; 

---

We are using more than 1 table to create the materialized view in Oracle. But the same approach will not work in Snowflake as the materialized view in Snowflake can query only a single table.

Any suggestions would be helpful.

Answer 1

Materialized View is coming as invalid materialized view definition in Snowflake. What is the alternate approach if we want to query more than one table?

A materialized view can query only a single table.

https://docs.snowflake.com/en/user-guide/views-materialized.html#limitations-on-creating-materialized-views

You can create a task to store the output of your query to a table, and refresh it periodically.