QSM-02025: no aggregate functions

QSM-02025: no aggregate functions

Cause: The capability in question is not supported when the materialized view uses no aggregate functions.

Action: Re-phrase the query to use aggregate functions.

QSM-02024: no GROUP BY clause

QSM-02024: no GROUP BY clause

Cause: The capability in question is not supported when the materialized view does not use a GROUP BY clause.

Action: Re-phrase the query to use a GROUP BY clause.

QSM-02023: nested cursor

QSM-02023: nested cursor

Cause: The capability in question is not supported when the materialized view uses a nested cursor expression.

Action: Re-phrase the query to avoid use of a nested cursor expression.

QSM-02022: aggregate selections

QSM-02022: aggregate selections

Cause: The capability in question is not supported when the materialized view uses an aggregate expression in the HAVING clause.

Action: Re-phrase the query to avoid the use of an aggregate in the HAVING clause.

QSM-02021: set operator encountered in mv

QSM-02021: set operator encountered in mv

Cause: The capability in question is not supported when the materialized view uses set operators such as UNION, UNION ALL, MINUS, etc.

Action: Re-phrase the query to avoid the use of set operators.

QSM-02020: container column not in SELECT list with NOT NULL constraint

QSM-02020: container column not in SELECT list with NOT NULL constraint

Cause: The capability in question is not supported when the materialized view s container table has a column that does not correspond to an output expression in the select list, and that column has a NOT NULL constraint. This situation can result in problems during refresh since any new rows inserted into the materialized view will set that column to NULL.

Action: Disable or remove the NOT NULL constraint.

QSM-02019: mv references another mv in FROM clause

QSM-02019: mv references another mv in FROM clause

Cause: The capability in question is not supported when the materialized view is nested (i.e., when it references another materialized view in its FROM list).

Action: Re-phrase the query to avoid reference to the other materialized view.

QSM-02018: subquery present in the WHERE clause

QSM-02018: subquery present in the WHERE clause

Cause: The capability in question is not supported when the materialized view uses a subquery in the WHERE clause.

Action: Re-phrase the query to avoid the subquery in the WHERE clause.

QSM-02017: subquery present in the HAVING clause

QSM-02017: subquery present in the HAVING clause

Cause: The capability in question is not supported when the materialized view uses a subquery in the HAVING clause.

Action: Re-phrase the query to avoid the subquery in the HAVING clause.

QSM-02016: clustered table in FROM list

QSM-02016: clustered table in FROM list

Cause: The capability in question is not supported when the materialized view references a clustered table in the FROM list.

Action: Remove the reference to the clustered table in the FROM list.

QSM-02015: Index Organized Table (IOT) present in FROM list

QSM-02015: Index Organized Table (IOT) present in FROM list

Cause: The capability in question is not supported when the materialized view references an Index Organized Table in the FROM list.

Action: Remove the reference to the Index Organized Table.

QSM-02014: GROUP BY expression other than a column

QSM-02014: GROUP BY expression other than a column

Cause: The capability in question is not supported when the materialized view uses an expression other than a simple column reference in the GROUP BY clause.

Action: Re-phrase the query to use only simple column expressions in the GROUP BY clause.

QSM-02013: SELECT list includes a RAW data type expression

QSM-02013: SELECT list includes a RAW data type expression

Cause: The capability in question is not supported when the materialized view uses RAW data type in an expression.

Action: Remove the reference to the RAW expression.

QSM-02012: a CONNECT BY clause is present

QSM-02012: a CONNECT BY clause is present

Cause: The capability in question is not supported when the materialized view uses a CONNECT BY clause.

Action: Re-phrase the query to avoid use of the CONNECT BY clause.

QSM-02011: a HAVING clause is present

QSM-02011: a HAVING clause is present

Cause: The capability in question is not supported when the materialized view uses a HAVING clause.

Action: Remove the HAVING clause.

QSM-02010: join predicate with operator other than equals (=)

QSM-02010: join predicate with operator other than equals (=)

Cause: The capability in question is not supported when the materialized uses a relationship operator other than equals ( = ) in a join in the WHERE clause. This is known as a non-equijoin.

Action: Re-phrase the query to avoid the non-equijoin.

QSM-02009: non-inner join

QSM-02009: non-inner join

Cause: The capability in question is only supported with inner joins.

Action: Re-phrase the query using an inner join. Remove any outer joins.

QSM-02008: non-join filter condition in WHERE or HAVING clause

QSM-02008: non-join filter condition in WHERE or HAVING clause

Cause: The capability in question is not supported when the materialized view has a selection condition (an expression not representing a join) in the WHERE or HAVING clauses.

Action: Remove the selection filter expression.

QSM-02007: multiple instances of the same table or view

QSM-02007: multiple instances of the same table or view

Cause: The capability in question is not supported when the same table or view occurs more than once in the FROM list.

Action: Re-phrase the query to avoid multiple instances of the same table. For instance,

QSM-02006: subquery in FROM list

QSM-02006: subquery in FROM list

Cause: The capability in question is not supported when a subquery appears in the FROM list.

Action: Remove the subquery from the FROM list.

QSM-02005: named view in FROM list

QSM-02005: named view in FROM list

Cause: The capability in question is not supported when a named view appears in the FROM list.

Action: Remove the named view from the FROM list.

QSM-02004: grouping column omitted from SELECT list

QSM-02004: grouping column omitted from SELECT list

Cause: The capability in question is not supported when the materialized view contains a column or expression in the GROUP BY clause that is not also present in the list of output select expressions.

Action: Include all GROUP BY expressions in the output select list.

QSM-02003: aggregate function nested within an expression

QSM-02003: aggregate function nested within an expression

Cause: The capability in question is not supported when the materialized view contains an aggregate function invocation nested within an outer expression.

Action: Re-phrase the expression such that the aggregate function invocation is not nested.

QSM-02002: DISTINCT aggregate measure in SELECT list

QSM-02002: DISTINCT aggregate measure in SELECT list

Cause: The capability in question is not supported when the materialized view uses the DISTINCT qualifier on an aggregate function.

Action: Remove the DISTINCT qualifier.

QSM-02001: clustered mv container table

QSM-02001: clustered mv container table

Cause: The capability in question is not supported on a materialized view that has a clustered container table.

Action: Recreate the materialized view using a conventional container table.

QSM-01621: join backs to base tables not allowed with an equivalence

QSM-01621: join backs to base tables not allowed with an equivalence

Cause: Query rewrite needs to do a join back to a base table column to answer this query. But join back is not allowed for an equivalence rewrite

Action: none

QSM-02179: MV with UNION ALL requires compatibility lamp;gt;= 9.2

QSM-02179: MV with UNION ALL requires compatibility lamp;gt;= 9.2

Cause: The setting of the COMPATIBLE initialization parameter is less than 9.2.0.

Action: Set COMPATIBLE to 9.2.0 or higher.

QSM-02178: MV has a view with UNION ALL that is not fast refreshable

QSM-02178: MV has a view with UNION ALL that is not fast refreshable

Cause: The materialized view has a view whose definition involves a UNION ALL query that does not satisfy the requirements for fast refresh.

Action: Check that the defining query of the view being referenced in the materialized view satisfies conditions for fast refresh of a UNION ALL materialized view.

QSM-02177: MV with UNION ALL in a view must match the view s SELECT list

QSM-02177: MV with UNION ALL in a view must match the view s SELECT list

Cause: If a materialized view has a view whose definition involves a UNION ALL query, then the materialized view s select list must match the underlying view s select list exactly. It must not have any expressions and must not have add or drop or reorder columns from the underlying view.

Action: Use select * when defining a materialized view with a UNION ALL in a view.

QSM-02176: MV with UNION ALL in a view is too complex

QSM-02176: MV with UNION ALL in a view is too complex

Cause: If a materialized view has a view whose definition involves a UNION ALL query, then the materialized view s defining query cannot have any clauses other than SELECT or FROM. Other clauses such as WHERE, HAVING, CONNECT BY, or GROUP BY are not allowed. Further more, the FROM clause should have only one item.

Action: Remove any clauses besides SELECT and FROM from the materialized view s defining query.

QSM-02175: some query block in UNION ALL MV is not fast refreshable

QSM-02175: some query block in UNION ALL MV is not fast refreshable

Cause: For a materialized view with UNION ALL to be fast refreshable, each query block must conform to a fast refreshable materialized join view or materialized aggregate view.

Action: Check each query block in the UNION ALL to ensure it is either a materialized view with joins or aggregates.

QSM-02174: the materialized view does not have a UNION ALL marker column

QSM-02174: the materialized view does not have a UNION ALL marker column

Cause: For a materialized view with UNION ALL to be fast refreshable, it must have a UNION ALL marker column. This is a constant column, at the same ordinal position in each query block and must have distinct values in each query block.

Action: Add a UNION ALL marker column to each query block in the UNION ALL query defining the materialized view.

QSM-02173: the unique constraints does not exist on the join columns of the inner table

QSM-02173: the unique constraints does not exist on the join columns of the inner table

Cause: If there are outer joins, unique constraints must exist on the join columns of the inner table. Othwerwise, the materialized view can not be fast refreshable.

Action: none

QSM-02171: the materialized view has a join operation in the outer UNION block

QSM-02171: the materialized view has a join operation in the outer UNION block

Cause: Fast refresh is not supported if a materialized view having the UNION operator contains a join operation.

Action: Rewrite the materialized view query without the join.

QSM-02170: tables must be identical across the UNION operator

QSM-02170: tables must be identical across the UNION operator

Cause: You have specified a materialized view using the UNION operator and the tables on each side of the UNION operator do not match. Fast refresh is not supported in this context.

Action: Restructure the materialized view query such that the respective sides of the UNION operator have the same table.

QSM-02169: the materialized view contains partition extended table name

QSM-02169: the materialized view contains partition extended table name

Cause: Fast refresh of materialized aggregate views and or or materialized join views are not supported if they were defined using partition extended table names.

Action: Create the fast refreshable materialized view without using partition extended table names or create the materialized view as a complete refresh materialized view.

QSM-02163: cannot use object id columns from materialized view log

QSM-02163: cannot use object id columns from materialized view log

Cause: The materialized view log either does not have object id columns logged, or the timestamp associated with the object id columns is more recent than the last refresh time.

Action: A complete refresh is required before the next fast refresh. Add object id columns to the materialized view log, if required.

QSM-02162: the detail table does not have a materialized view log

QSM-02162: the detail table does not have a materialized view log

Cause: The fast refresh cannot be performed because the master table does not contain a materialized view log.

Action: Use the CREATE MATERIALIZED VIEW LOG command to create a materialized view log on the master table.

QSM-02161: see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

QSM-02161: see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

Cause: REFRESH_FAST_AFTER_ANY_DML is disabled for the same reason that REFRESH_FAST_AFTER_ONETAB_DML is disabled.

Action: Correct the problem with REFRESH_FAST_AFTER_ONETAB_DML.

QSM-02154: MV is not fast refreshable even with view merging

QSM-02154: MV is not fast refreshable even with view merging

Cause: You have a materialized view with view in the FROM clause, however it does not meet the criteria for fast refresh after view merging.

Action: Restructure the materialized view query to remove the offending view or the complex construct in the view.

QSM-02153: inline view or subquery in FROM list not supported for this type MV

QSM-02153: inline view or subquery in FROM list not supported for this type MV

Cause: You have specified a materialized view that references an inline view or a subquery. The indicated capability is not supported for this type of materialized view.

Action: Restructure the materialized view query.

QSM-02152: subquery or named view in FROM list even after view merging

QSM-02152: subquery or named view in FROM list even after view merging

Cause: You have specified a materialized view that references a subquery ore named view in its top level FROM list that could not be merged during the view merging process.

Action: Restructure the materialized view query.

QSM-02151: subquery or named view in FROM list and lamp;lt;lamp;nbsp; 9.0 compatibility

QSM-02151: subquery or named view in FROM list and lamp;lt;lamp;nbsp; 9.0 compatibility

Cause: You have specified a materialized view that references a subquery or named view in its top level FROM list while running in less than 9.0 compatibility mode.

Action: Set compatibility mode to 9.0 or higher or restructure the materialized view query.

QSM-02150: select lists must be identical across the UNION operator

QSM-02150: select lists must be identical across the UNION operator

Cause: You have specified a materialized view using the UNION operator and the respective queries on each side of the UNION operator do not have the same select list. The RELATED_NAME column shows the alias of the first different select list item. The RELATED_NUM column shows the offset from the SELECT keyword to the start of this select list item.

Action: Restructure the materialized view query such that the respective sides of the UNION operator have identical select lists.

QSM-02149: ON COMMIT with this MV requires 9.0 compatibility or higher

QSM-02149: ON COMMIT with this MV requires 9.0 compatibility or higher

Cause: You have specified a materialized view that uses some combination of the following: joins aggregation filter conditions in the WHERE clause a remote relation in the FROM list a SUM(x) function without a corresponding COUNT(x) function an omitted COUNT(*) aggregate function a MIN() or MAX() aggregate function in combination with the REFRESH ON COMMIT option while running in less than 9.0 compatibility mode.

Action: Set your compatibility mode to 9.0 or higher or restructure the materialized view query.

QSM-02148: many to many subquery joins require 9.0 compatibility or higher

QSM-02148: many to many subquery joins require 9.0 compatibility or higher

Cause: You have specified a many to many join in your materialized view while running in less than 9.0 compatibility mode.

Action: Set your compatibility mode to 9.0 or higher or restructure the materialized view query.

QSM-02147: default date format is sensitive to session settings

QSM-02147: default date format is sensitive to session settings

Cause: You have specified a date conversion operation using either the default format or an incomplete format. The default date format or an incomplete format is sensitive to session settings, and therefore may not be reproducible across different environments.

Action: Specify a complete date format string.

QSM-02146: see the reason why REFRESH_FAST_AFTER_INSERT is disabled

QSM-02146: see the reason why REFRESH_FAST_AFTER_INSERT is disabled

Cause: REFRESH_FAST_AFTER_ONETAB_DML is disabled for the same reason that REFRESH_FAST_AFTER_INSERT is disabled.

Action: Correct the problem with REFRESH_FAST_AFTER_INSERT.

QSM-02145: GROUP BY clause is present but no aggregate functions are used

QSM-02145: GROUP BY clause is present but no aggregate functions are used

Cause: A GROUP BY clause is used but no aggregate functions are present in the select list.

Action: Add an aggregate function to the select list.

QSM-02144: aggregate functions are present without a GROUP BY clause

QSM-02144: aggregate functions are present without a GROUP BY clause

Cause: One or more aggregate functions are present in the select list but a GROUP BY clause is not used.

Action: Add a GROUP BY clause.

QSM-02143: SUM(expr) without COUNT(expr)

QSM-02143: SUM(expr) without COUNT(expr)

Cause: SUM(expr) occurs in the select list without a corresponding COUNT(expr).

Action: Add COUNT(expr) to the select list.

QSM-02142: COUNT(*) is not present in the select list

QSM-02142: COUNT(*) is not present in the select list

Cause: The materialized view query omits COUNT(*) from the select list.

Action: Add COUNT(*) to the select list.

QSM-02141: the reason why the capability is disabled has escaped analysis

QSM-02141: the reason why the capability is disabled has escaped analysis

Cause: The EXPLAIN_MV analysis engine has failed to capture the reason why the given capability is not possible.

Action: Report the problem through your normal problem reporting channels.

QSM-02140: agg(expr) requires correspondng SUM(expr*expr) function

QSM-02140: agg(expr) requires correspondng SUM(expr*expr) function

Cause: The capability in question is not supported when the materialized view uses certain aggregate functions but omits an invocation of the SUM function on the square of the same argument (the argument multiplied by itself).

Action: Add the SUM function on the square of the argument.

QSM-02139: agg(expr) requires correspondng agg(expr+expr) function

QSM-02139: agg(expr) requires correspondng agg(expr+expr) function

Cause: The capability in question is not supported when the materialized view uses certain aggregate functions but omits an invocation of that same aggregate function on the same argument added to the same argument.

Action: Add the indicated aggregate function to the select list.

QSM-02138: agg(expr) requires correspondng agg(expr*expr) function

QSM-02138: agg(expr) requires correspondng agg(expr*expr) function

Cause: The capability in question is not supported when the materialized view uses certain aggregate functions but omits an invocation of that same aggregate function on the square of the same argument.

Action: Add an invocation of the same aggregate function on the square of the same argument (that is, the argument multiplied by itself).

QSM-02137: agg(expr) requires correspondng STDDEV(expr) function

QSM-02137: agg(expr) requires correspondng STDDEV(expr) function

Cause: The capability in question is not supported when the materialized view uses certain aggregate functions but omits an invocation of the STDDEV function on the same argument.

Action: Add the STDDEV function to the select list.

QSM-02136: agg(expr) requires correspondng VARIANCE(expr) function

QSM-02136: agg(expr) requires correspondng VARIANCE(expr) function

Cause: The capability in question is not supported when the materialized view uses certain aggregate functions but omits an invocation of the VARIANCE function on the same argument.

Action: Add the VARIANCE function to the select list.

QSM-02135: agg(expr) requires correspondng AVG(expr) function

QSM-02135: agg(expr) requires correspondng AVG(expr) function

Cause: The capability in question is not supported when the materialized view uses certain aggregate functions but omits an invocation of the AVG function on the same argument.

Action: Add the AVG function to the select list.

QSM-02134: agg(expr) requires correspondng MAX(expr) function

QSM-02134: agg(expr) requires correspondng MAX(expr) function

Cause: The capability in question is not supported when the materialized view uses certain aggregate functions but omits an invocation of the MAX function on the same argument.

Action: Add the MAX function to the select list.