-20058 THE FULLSELECT SPECIFIED FOR MATERIALIZED QUERY TABLE table-name IS NOT VALID.
Explanation
The materialized query table definition has specific rules regarding the contents of the fullselect, and the fullselect that was specified did not conform to these rules.
- table-name
- The name of the materialized query table.
General restrictions: The
following restrictions apply:
- The length of each result column of the fullselect must not be 0.
- The fullselect cannot contain a column of a LOB or XML data type.
- No more than one table in the fullselect can contain a security label column.
- The fullselect must not contain a period specification.
- The object that is specified in the FROM clause of the fullselect cannot be a view with columns of length 0.
- The fullselect cannot contain a reference to a created global temporary table, a declared global temporary table, an accelerator-only table, or another materialized query table.
- The fullselect cannot directly or indirectly reference a base table that has been activated for the row or column access control or a base table for which a row permission or a column mask has been defined.
- The fullselect must not refer to host variables or include parameter markers.
Additional restrictions when ENABLE QUERY OPTIMIZATION is in effect:
- The fullselect must be a subselect.
- The outermost SELECT list of the subselect must not reference data that is encoded with different CCSID sets.
- The subselect cannot include the following:
- A special register
- A scalar fullselect
- A row change timestamp column
- A ROW CHANGE expression
- An expression for which implicit time zone values apply (for example, cast a timestamp to a timestamp with time zone)
- The RAND built-in function
- The RID built-in function
- A user-defined scalar or table function that is not deterministic or that has external actions
- Any predicates that include a subquery
- A row expression predicate
- A join using the INNER JOIN syntax, or an outer join
- A lateral correlation
- a nested table expression or view that requires temporary materialization
- A direct or indirect reference to a table that uses activated row or column access controls, or a table for which row or column access controls have been defined.
- A FETCH FIRST clause
- If a table with a security label is referenced, the security label column must be referenced in the outer select list of the subselect.
- If the subselect references a view, the fullselect in the view definition must satisfy all other restrictions.
System action
The statement cannot be processed.
Programmer response
Change the fullselect in the CREATE TABLE or ALTER TABLE statement so that it conforms to the rules listed above.
SQLSTATE
428EC
