Materialized View messages (12001 through 12329)
SQL 12001
12001 Creation failed. The materialized view cannot be maintained incrementally.
Cause
The materialized view definition contains an expression or function that cannot be maintained incrementally. For example, subquery constructs such as EXISTS, NOT EXISTS, IN NOT IN, and expressions or functions involving more than one column from the constituent tables.
Effect
The operation fails.
Recovery
Remove the unsupported clauses or expressions, and resubmit the statement.
SQL 12008
12008 Creation of materialized view failed.
Cause
The materialized view is not created due to the unavailability of all the disks in the partition specification, existence of a file or object with the same name, duplicate column reference in materialized view definition, and other such issues.
Effect
The operation fails.
Recovery
Other error messages appear with this one. Use them to diagnose and correct the problem.
SQL 12009
12009 Object object-name cannot be dropped because it is being used by materialized view materialized-view-name.
-
Where,
-
object-name is the name of the object that is being dropped.
-
materialized-view-name is the name of the materialized view dependent on the object-name.
Cause
You issued a DROP statement against an object which has a dependent materialized view on it.
Effect
The operation fails.
Recovery
Drop the dependent materialized view and resubmit the statement or use the CASCADE option with the DROP statement.
SQL 12013
12013 Initialization of the materialized view failed.
Cause
The materialized view is not initialized due to the unavailability of all the disks in the partition specification of materialized view or of the table and so on. EXTENT is insufficient.
Effect
The operation fails.
Recovery
Other error messages appear with this one depending on what caused the initialization error. Use them to diagnose and correct the problem.
SQL 12014
-
Where,
-
object-name is the name of the object for which the system tries to acquire a lock and fails.
Cause
The materialized view is not initialized because the system is unable to lock all the required partitions of the constituent objects, all the disks in the partition specification of materialized view or of the table are not available, and other such issues.
Effect
The operation fails.
Recovery
Other error messages appear with this one depending on what caused the initialization error. Use them to diagnose and correct the problem.
SQL 12016
12016 Unique indexes are not allowed on materialized views.
Cause
You tried to create a unique index on a materialized view.
Effect
The operation fails.
Recovery
Do not create user indexes (including unique indexes) on the materialized view.
SQL 12027
12027 Materialized views cannot be created on metadata table object-name.
-
Where,
-
object-name is the name of the metadata table specified in the materialized view definition.
Cause
One of the tables specified in the materialized view definition is a metadata table.
Effect
The operation fails.
Recovery
Do not create the materialized view on metadata tables.
SQL 12041
12041 STORE BY ENTRY ORDER is not supported for materialized views.
Cause
You specified the STORE BY clause as ENTRY ORDER in the MV definition.
Effect
The operation fails.
Recovery
Do not specify STORE BY option as ENTRY ORDER, while creating an MV.
SQL 12042
12042 STORE BY PRIMARY KEY is not supported for materialized views.
Cause
You specified the STORE BY clause as PRIMARY KEY in the MV definition.
Effect
The operation fails.
Recovery
Do not specify STORE BY option as PRIMARY KEY, while creating an MV.
SQL 12052
12052 The specified attribute is not supported for ALTER MATERIALIZED VIEW.
Cause
ALTER MATERIALIZED VIEW command attempted an unsupported attribute change.
Effect
The operation fails.
Recovery
Currently, ALTER MATERIALIZED VIEW command supports only ANSI RENAME option.
SQL 12053
12053 An attribute list syntax error occurred.
Cause
ALTER MATERIALIZED VIEW command attempted an unsupported attribute change.
Effect
The operation fails.
Recovery
Currently, ALTER MATERIALIZED VIEW command supports only ANSI RENAME option.
SQL 12065
12065 Stream expression is not supported for materialized views.
Cause
The materialized view definition contains a stream or an expression or function on a stream.
Effect
The operation fails.
Recovery
Create materialized views only on user tables.
SQL 12066
12066 The embedded operation expression is not supported for materialized views.
-
Where,
-
operation is either INSERT, UPDATE or DELETE.
Cause
The materialized view definition contains an embedded INSERT, UPDATE, or DELETE statement.
Effect
The operation fails.
Recovery
Remove embedded INSERT, UPDATE, and DELETE expressions or subqueries from the materialized view definition.
SQL 12074
12074 A materialized view cannot be updated directly.
Cause
IUD statements were attempted directly on materialized view.
Effect
The operation fails.
Recovery
Do not update the materialized views directly.
SQL 12112
-
Where,
-
index-name is the name of the system created index on the materialized view.
Cause
SQL/MX created a system index on the materialized view.
Effect
The operation is successful.
Recovery
None. This is a warning only.
SQL 12113
12113 ON STATEMENT MAVs and MAJVs are currently not supported.
Cause
The materialized view definition contains an aggregate expression or function or a group by clause with or without joining two or more tables.
Effect
The operation fails.
Recovery
Use equi-join only with ON STATEMENT materialized views.
SQL 12118
-
Where,
-
operation-name is the name of a DDL operation.
Cause
You attempted to create an object on the materialized view as a dependent object.
Effect
The operation fails.
Recovery
Do not create objects, such as INDEX or TRIGGER, on a materialized view.
SQL 12120
-
Where,
-
operation is name of a DDL operation. It is either MATERIALIZED VIEW or TRIGGER.
-
object is the type of the object that exists on the table-name.
-
table-name is the subject table name.
Cause
You attempted to create a trigger on a table on which a materialized view exists. Or, you created a materialized view on a table on which a trigger exists. Triggers and materialized views cannot coexist on a table.
Effect
The operation fails.
Recovery
Drop the conflicting object and resubmit the statement.
SQL 12121
12121 Materialized view creation failed because an invalid query or expression was specified.
Cause
You attempted to use the invalid query, or unsupported function or expression N such as ROW COUNT in the materialized view definition.
Effect
The operation fails.
Recovery
Remove the invalid query, or unsupported function or expression N such as ROW COUNT from the materialized view definition and resubmit the statement.
SQL 12318
12318 FIRST/ANY is not allowed in a query expression of a materialized view.
Cause
You attempted to specify a FIRST N or ANY N expression in the materialized view definition.
Effect
The operation fails.
Recovery
Remove the expression involving FIRST N or ANY N from the materialized view definition and resubmit the statement.
SQL 12321
12321 Creation failed. The select list must have at least one column. The materialized view cannot be maintained incrementally.
Cause
The select list in the materialized view definition does not contain any valid column name or reference.
Effect
The operation fails.
Recovery
Correct the definition and retry the statement.
SQL 12322
12322 Creation failed. The same table is used more than once. Incremental refresh type cannot be specified.
Cause
You attempted to reference or use the same table more than once in the materialized view definition.
Effect
The operation fails.
Recovery
Use a table only once in the materialized view definition and resubmit the statement.
SQL 12323
12323 Creation failed. The selection predicate includes a non-repeatable expression. Incremental refresh type cannot be specified.
Cause
You attempted to specify a non repeatable expression in the materialized view definition.
Effect
The operation fails.
Recovery
Remove the non repeatable expression from the materialized view definition and resubmit the statement.
SQL 12324
12324 Creation failed. The statement includes multiple grouping clauses. Incremental refresh type cannot be specified.
Cause
You specified multiple grouping expressions in the MV definition, such as both DISTINCT and GROUP BY clauses.
Effect
The operation fails.
Recovery
Remove one of the grouping clauses and resubmit the statement.
SQL 12325
12325 Creation failed. The statement includes a HAVING clause. Incremental refresh type cannot be specified.
Cause
You attempted to add HAVING clause in the materialized view definition.
Effect
The operation fails.
Recovery
The aggregate functions, HAVING, and GROUP BY clauses are not supported in MV definition. Remove these functions from the materialized view definition and resubmit the statement.
SQL 12326
12326 Creation failed. Cross joins without equal join predicates are not supported. Incremental refresh type cannot be specified.
Cause
The materialized view definition contains only equi-join conditions.
Effect
The operation fails.
Recovery
Specify only equi-join conditions and resubmit the statement.
SQL 12327
12327 Creation failed. The select list contains a non-grouping, non-aggregated column. Incremental refresh type cannot be specified.
Cause
The materialized view definition contains a pseudo-aggregate expression on a column but without any grouping column.
Effect
The object creation fails.
Recovery
The materialized views can be created without non-grouping and non-aggregate columns and expressions.
SQL 12328
12328 Creation failed. Only equi-join predicates are allowed. Incremental refresh type cannot be specified.
Cause
You attempted to add non equi-join conditions in the materialized view definition.
Effect
The operation fails.
Recovery
Specify equi-join conditions on the columns of the tables being joined and resubmit the statement.
SQL 12329
12329 Creation failed. Join predicates can have only one column instance on each side of the equal sign. Incremental refresh type cannot be specified.
Cause
The materialized view definition contains more than one column on any side of equality join predicate.
Effect
The operation fails.
Recovery
Correct the unsupported expressions and resubmit the statement.
SQL 20240
12329 Creation failed. Join predicates can have only one column instance on each side of the equal sign. Incremental refresh type cannot be specified.
Cause
The materialized view definition contains more than one column on any side of equality join predicate.
Effect
The operation fails.
Recovery
Correct the unsupported expressions and resubmit the statement.