sa_materialized_view_can_be_immediate system procedure

Returns whether the specified materialized view can be defined as immediate.

Syntax
sa_materialized_view_can_be_immediate( 
view_name 
, owner_name
)
Arguments
  • view_name   Use this CHAR(128) parameter to specify the name of the materialized view. If view_name is NULL, a Table...not found error is returned.

  • owner_name   Use this CHAR(128) parameter to specify the owner of the materialized view. If owner_name is NULL, a Table...not found error is returned.

Remarks

There are restrictions on whether the specified manual view can be changed to an immediate view. Use this system procedure to determine whether the change is permitted. For a list of the additional restrictions for creating immediate views, see Additional restrictions for immediate views.

The sa_materialized_view_can_be_immediate system procedure returns the following information for the specified materialized view.

Column name Data type Description
SQLStateVal CHAR(6) The SQLSTATE returned.
ErrorMessage LONG VARCHAR The error message corresponding to the SQLSTATE.

Each row in the result set corresponds to a single SQLSTATE returned for a view. Consequently, if the materialized view definition violates more than one restriction, the results include multiple rows for the view.

You can combine the output of this system procedure with the output of the sa_materialized_view_info system procedure to get information on the status of views and whether they can be made immediate. See the Example section of sa_materialized_view_info system procedure.

Permissions

DBA authority, or execute permissions on DBO owned procedures.

See also
Side effects

All metadata for the specified materialized view, and all dependencies, are loaded into the server cache.

Example

Execute the following statements to create a manual view, view10, and refresh it:

CREATE MATERIALIZED VIEW view10
   AS (SELECT C.ID, C.Surname, sum(P.UnitPrice) as revenue, C.CompanyName, SO.OrderDate
         FROM Customers C, SalesOrders SO, SalesOrderItems SOI, Products P
         WHERE C.ID = SO.CustomerID
         AND SO.ID = SOI.ID
         AND P.ID = SOI.ProductID
         GROUP BY C.ID, C.Surname, C.CompanyName, SO.OrderDate);
REFRESH MATERIALIZED VIEW view10;

Use the following query to find the reasons why view10 cannot be changed to an immediate view:

SELECT SQLStateVal AS "SQLstate", ErrorMessage AS Description 
   FROM sa_materialized_view_can_be_immediate( 'view10', 'DBA' ) 
   ORDER BY SQLSTATE;
SQLstate Description
42WC3 The materialized view view10 cannot be changed to immediate because it has already been initialized.
42WCA The materialized view view10 cannot be changed to immediate because it does not have a unique index on non-nullable columns.
42WC6 The materialized view cannot be changed to immediate because COUNT(*) is required to be part of the SELECT list.
42WC7 The materialized view cannot be changed to immediate because it does not have a unique index on non-aggregate non-nullable columns.