Start of change

-20093   THE TABLE table-name CANNOT BE CONVERTED TO OR FROM A MATERIALIZED QUERY TABLE, OR THE MATERIALIZED QUERY TABLE PROPERTY CANNOT BE ALTERED. REASON CODE = reason-code.

Explanation

The ALTER TABLE statement was not able to make one of the following changes:

  • Change a table from a materialized query table to a base table.
  • Change a base table to a materialized query table.
  • Change the materialized query table properties.
reason-code
The reason for the message or SQL code, indicated by one of the following values:
2
The table is not a materialized query table, and DROP MATERIALIZED QUERY is specified.
4
The table has at least one trigger defined.
5
The table has at least one check constraint defined.
6
The table has at least one unique constraint or index or has hash organization.
7
The table has at least one referential constraint defined.
8
The table is referenced in one of the following definitions:
  • The definition of an existing materialized query table
  • The definition of a view when altering to a system-maintained materialized query table
9
The table is referenced directly or indirectly (through a view, for example) in the fullselect.
10
The table is already a materialized query table.
11
The number of columns of the existing table does not match the number of columns that are defined in the select list of the fullselect.
12
The data types of the columns of the existing table do not exactly match the corresponding columns in the select list of the fullselect.
13
The column names of the columns of the existing table do not exactly match the corresponding column names in the select list of the fullselect.
14
The nullability, default, or other characteristics of the columns of the existing table do not match the characteristics of the corresponding columns in the select list of the fullselect.
15
The conversion cannot be performed if the same ALTER TABLE statement has other table alterations.
16
The table is not a materialized query table, and alteration of materialized query table properties was specified.
17
The table identifies a base table that is activated for row or column access control or for which a row permission or a column mask is defined.
19
The table cannot be defined as a materialized query table because it is a system-period temporal table or a history table.

System action

The statement cannot be processed.

Programmer response

Take the action that corresponds to the reason-code value:

2
You do not need to convert this table. No action is required.
4
Drop any triggers, and resubmit the ALTER TABLE statement.
5
Drop any check constraints, and resubmit the ALTER TABLE statement.
6
Drop any unique constraint and unique indexes, and resubmit the ALTER TABLE statement.
7
Drop the referential constraints, and resubmit the ALTER TABLE statement.
8
Drop the materialized query table that references the table, and resubmit the ALTER TABLE statement.
9
A materialized query table cannot reference itself. Modify the fullselect to remove the direct or indirect reference to the table that is being altered.
10
The operation is not allowed since the table is already a materialized query table.
11
Modify the fullselect to include the correct number of columns in the select list.
12
Modify the fullselect so that the result column data types exactly match the data types of the corresponding existing columns.
13
Modify the fullselect so that the result column names exactly match the column names of the corresponding existing columns.
14
The table cannot be converted to a materialized query table unless the nullability characteristics can be matched. Create a new materialized query table instead.
15
Perform the other table alterations in an ALTER TABLE statement that do not include the ADD MATERIALIZED QUERY clause.
16
Take one of the following actions:
  • Correct the table name to specify a materialized query table.
  • Use the ALTER TABLE statement to convert the table to a materialized query table with the required properties.
17
Take one of the following actions:
  • Deactivate the access control and drop row permissions and column masks for the table.
  • Choose a different base table to convert to a materialized query table.
19
Take one of the following actions:
  • Correct the table name to specify a table that is not a system maintained temporal table or history table.
  • Alter the system-period temporal table with an ALTER TABLE statement that includes a DROP VERSIONING clause.

SQLSTATE

428EW

End of change