-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