[sql] Failed to execute: CREATE MATERIALIZED VIEW <Dataschema>.MKT_CAMPAIGN_OFFER_STATS (LAST_REFRESHED, ISSUENAME, GROUPNAME, OFFER, PENDING, IMPRESSION, CLICKED, ACCEPTED) NOCACHE LOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE REFRESH COMPLETE NEXT SYSDATE + 30/1440 AS WITH all_stats AS (SELECT ifr.pxfactid AS pxfactid ,ifr.pysubjectid AS customerid ,CASE isr.pyoutcome WHEN 'Pending' THEN 1 ELSE 0 END AS pending ,CASE isr.pyoutcome WHEN 'Impression' THEN 1 ELSE 0 END AS impression ,CASE isr.pyoutcome WHEN 'Clicked' THEN 1 ELSE 0 END AS clicked ,CASE isr.pyoutcome WHEN 'Accepted' THEN 1 ELSE 0 END AS accepted ,irc.pycategory AS pycategory ,action.pyissue AS pyissue ,action.pygroup AS pygroup ,action.pyname AS offer FROM CDHIH.pr_data_ih_fact ifr LEFT OUTER JOIN CDHIH.pr_data_ih_dim_context irc ON (ifr.pzcontextid = irc.pzid) INNER JOIN CDHIH.pr_data_ih_dim_outcome isr ON (ifr.pzoutcomeid = isr.pzid) LEFT OUTER JOIN CDHIH.pr_data_ih_dim_action action ON (ifr.pzactionid = action.pzid) WHERE isr.pyoutcome IN ('Pending' ,'Impression' ,'Clicked' ,'Accepted') AND irc.pycategory LIKE 'Data-BatchOutCMPR%' WITH READ ONLY) ,offer_category_customer AS (SELECT MAX (impression) AS impression ,MAX (clicked) AS clicked ,MAX (accepted) AS accepted ,MAX (pending) AS pending ,pyissue ,pygroup ,offer ,pycategory ,customerid FROM all_stats GROUP BY pyissue ,pygroup ,offer ,pycategory ,customerid) SELECT SYSDATE AS last_refreshed ,pyissue AS issuename ,pygroup AS groupname ,offer ,SUM (pending) AS pending ,SUM (impression) AS impression ,SUM (clicked) AS clicked ,SUM (accepted) AS accepted FROM offer_category_customer GROUP BY pyissue, pygroup, offer
[sql] java.sql.SQLException: ORA-12006: Snapshot of zonemap "<Dataschema>"."MKT_CAMPAIGN_OFFER_STATS" bestaat al.
[sql] Failed to execute: COMMENT ON MATERIALIZED VIEW <Dataschema>.MKT_CAMPAIGN_OFFER_STATS IS 'snapshot table for snapshot MKT_CAMPAIGN_OFFER_STATS'
The DROP TABLE command is failing because the table is already a materialized view. However it also appears that the user that is running the command does not have the privileges needed to drop a materialized view in another schema. Therefore the DROP MATERIALIZED VIEW statement is failing. Since it is not dropped, the subsequent CREATE MATERIALIZED VIEW statement fails as it already exists. The existing MV can be dropped either by the dataschema user, or by the deployment user if granted the DROP ANY MATERIALIZED VIEW privilege.
From the file, it appears that your deployment user only has the CREATE ANY MATERIALIZED VIEW privilege. It also needs to have the DROPANY MATERIALIZED VIEW privilege so that it can drop the MV in the PegaDATA schema. This step appears to be missing from the documentation. If this privilege cannot be granted, you could also log in as the PegaDATA user and drop the materialized view prior to running the setup utility.