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
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;
Snowflake
"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
;
Related EWIs
MSCEWI1021: NODE NOT SUPPORTED
Last updated
Was this helpful?