SnowflakeDocumentation Home

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

PreviousMSCINF0001NextMSCINF0003

Last updated