Creating primary key on materialized view

CREATE MATERIALIZED VIEW "ARIEL"."MDFRPT" ("Custnum", "CM_CUSTMR_NAME", "CM_CUSTMR_ADDR_1", "PHONE", "CM_CONTACT", "CM_CHNID", "30", "Current Period", "CP With Ticket", "Second Period", "Third Period", "Fourth Period", "Total Balance") ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SALES_ARIELS" BUILD IMMEDIATE USING INDEX REFRESH COMPLETE ON DEMAND USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
                   AS
  SELECT CM_CUSTNO AS "Custnum",
    cm_custmr_name,
    cm_custmr_addr_1,
    cm_phnarea
    ||cm_phnnbr AS phone,
    cm_contact,
    cm_chnid,
    30,
    NVL(SUM(
    CASE
      WHEN sdate BETWEEN ROUND(SYSDATE) - (30) AND ROUND(SYSDATE)
      THEN NVL(ar_amount,0)             +ar_adjamt-ar_paymts
    END),0) AS "Current Period",
    NVL(SUM(
    CASE
      WHEN sdate BETWEEN ROUND(SYSDATE) - (30) AND ROUND(SYSDATE)
      THEN ar_amount                    +ar_adjamt-ar_paymts
    END)                                +NVL((cur),0) +NVL(
    (SELECT SUM(tk_tktotl) FROM ticket@"DB2" WHERE tk_custno=cm_custno
    ),0),0) AS "CP With Ticket",
    NVL(SUM(
    CASE
      WHEN sdate BETWEEN ROUND((SYSDATE - (30)) - (30)) AND ROUND(SYSDATE - 30)
      THEN NVL(ar_amount                +ar_adjamt-ar_paymts,0)
    END),0) AS "Second Period",
    NVL(SUM(
    CASE
      WHEN sdate BETWEEN ROUND(SYSDATE - ((30)*3)) AND ROUND(SYSDATE - ((30)*2))
      THEN ar_amount                   +ar_adjamt-ar_paymts
    END),0) AS "Third Period",
    NVL(SUM(
    CASE
      WHEN sdate < ROUND(SYSDATE - ((30)*3))
      THEN ar_amount             +ar_adjamt-ar_paymts
    END),0)   AS "Fourth Period",
    cm_nowdue AS "Total Balance"
  FROM
    (SELECT cm_custno,
      cm_terms,
      cm_custmr_name,
      cm_custmr_addr_1,
      cm_nowdue,
      cm_bilcod,
      cm_phnarea,
      cm_phnnbr,
      cm_contact,
      cm_chnid,
      at_due_days,
      cm_wk_dols AS cur,
      ar_paymts,
      ar_adjamt,
      ar_amount,
      TO_DATE((ar_tranda)
      || '-'
      || ar_tranmo
      || '-'
      || ar_trancn
      || ar_tranyr, 'DD-MM-YYYY') AS sdate
    FROM (cusmas@"DB2"
    INNER JOIN arterm@"DB2"
    ON cusmas.cm_terms = arterm.at_terms)
    INNER JOIN accrec@"DB2"
    ON accrec.ar_custno = cusmas.cm_custno
    )
  WHERE CM_BILCOD = 2
  AND cm_custno  IN (1606,1608,1609,1611,1614,1618,1619,1620,1621,9700,9701,9702,9706,9707)
  GROUP BY cm_custno,
    cm_nowdue,
    cm_chnid,
    cm_phnarea,
    cm_phnnbr,
    cm_contact,
    cm_custmr_name,
    cm_custmr_addr_1,
    cur;
  COMMENT ON MATERIALIZED VIEW "ARIEL"."MDFRPT"
IS
  'snapshot table for snapshot ARIEL.MDFRPT';

Select allOpen in new window