Avatar of futureDBA
futureDBA
 asked on

Creating primary key on materialized view

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

Oracle Database

Avatar of undefined
Last Comment
futureDBA

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
futureDBA

ASKER
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.
futureDBA

ASKER
figured it out, thanks
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck