[OLD] SnowConvert - LIMITED ACCESS

Ctrlk

Materialized Query Definition

Description

Materialized query tables (MQTs) are tables whose definition is based on the result of a query.

MATERIALIZED QUERY is not supported in Snowflake.

Click here to navigate to the IBM DB2 docs page for this syntax.

Grammar Syntax

Sample Source Patterns

IBM DB2

Snowflake

MSCEWI1021: NODE NOT SUPPORTED

PreviousAs Result Table NextStaging Table Definition

Last updated 3 years ago

Was this helpful?

CREATE TABLE TRANSCNT (ACCTID, LOCID, YEAR, CNT) AS
  (SELECT ACCOUNTID, LOCATIONID, YEAR, COUNT(*)
     FROM TRANS
     GROUP BY ACCOUNTID, LOCATIONID, YEAR )
     DATA INITIALLY DEFERRED
     REFRESH DEFERRED
     MAINTAINED BY SYSTEM
     ENABLE QUERY OPTIMIZATION;

"CREATE TABLE PUBLIC.TRANSCNT (ACCTID, LOCID, YEAR, CNT) AS
(SELECT ACCOUNTID, LOCATIONID, YEAR, COUNT(*)
   FROM TRANS
   GROUP BY ACCOUNTID, LOCATIONID, YEAR )
-- ** MSC-ERROR - MSCEWI1021 - MATERIALIZED QUERY IS NOT SUPPORTED **
--   DATA INITIALLY DEFERRED
--   REFRESH DEFERRED
--   MAINTAINED BY SYSTEM
--   ENABLE QUERY OPTIMIZATION
                            ;