MSCINF0002

Statement references multiple objects

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

None

Description

Materialized views in Snowflake cannot query more than a single object. If SnowConvert would produce a CREATE MATERIALIZED VIEW that has more than one object reference (tables, views, result sets...), then the materialized behavior wouldn't be supported.

In most cases, SnowConvert automatically detects this issue, adds an informative EWI to the problematic object, and outputs a regular CREATE VIEW object instead.

Code Example

Input Code:

-- This SQL Server MATERIALIZED VIEW references dbo.Categories and dbo.Products,
-- thus making it a non-valid Snowflake MATERIALIZED VIEW
CREATE MATERIALIZED VIEW dbo.MyMaterializedView
AS
SELECT 
    *
FROM
    dbo.Categories c
JOIN
    dbo.Products p ON c.CategoryID = p.CategoryID
GROUP BY
    c.CategoryID,
    c.CategoryName;

Output Code:

/*** MSC-INFORMATION - MSCINF0002 - CREATE MATERIALIZED VIEW REFERENCES MULTIPLE OBJECTS. SNOWFLAKE MATERIALIZED VIEWS CANNOT QUERY MORE THAN A SINGLE OBJECT. ***/
CREATE OR REPLACE VIEW dbo.MyMaterializedView
AS
SELECT
    *
FROM
    dbo.Categories c
JOIN
    dbo.Products p
    ON c.CategoryID = p.CategoryID
GROUP BY
    c.CategoryID,
    c.CategoryName;

Recommendations

• Remember that access to materialized views in SnowFlake requires Enterprise Edition or higher.
• For more support, you can email us at [e-mail] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.