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.
The sa_materialized_view_can_be_immediate system procedure returns the following information for the specified materialized
The SQLSTATE returned.
The error message corresponding to the SQLSTATE.
Each row in the result set corresponds to a single SQLSTATE returned for a view. So, 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 about the status of views and whether they can be made immediate.
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;