Creating primary key on materialized view

futureDBA
futureDBA used Ask the Experts™
on
I have create a materialized view using the code below

but when i try add a pk using  "alter table MDFRPT add constraint mdfrpd_pk primary key (Custnum);"

I get error

SQL Error: ORA-00904: "CUSTNUM": invalid identifier
00904. 00000 -  "%s: invalid identifier"


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';

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
the problem is you are using case-sensitive names.

if you put the name in double-quotes when you declare the column, you must use double quotes in all references thereafter

Custnum  isn't correct

use

"Custnum"  

or, change the CREATE of the MV to NOT use double-quotes on the column aliases

Author

Commented:
that seems to have worked but it is defaulting to SYSTEM tablespace

1) how can i specify tablespace on an alter
2) can i make it default to my current tablespace when ever i am working with this user?



Error starting at line 1 in command:
alter table MDFRPT add constraint mdfrpd_pk primary key ("Custnum")
Error report:
SQL Error: ORA-01950: no privileges on tablespace 'SYSTEM'
01950. 00000 -  "no privileges on tablespace '%s'"
*Cause:    User does not have privileges to allocate an extent in the
           specified tablespace.
*Action:   Grant the user the appropriate system privileges or grant the user
           space resource on the tablespace.

Author

Commented:
figured it out, thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial