Create Primary Key on View

From: js <androidsun_at_yahoo.com>
Date: 8 Oct 2003 16:00:19 -0700
Message-ID: <23869fd0.0310081500.21e0d68b_at_posting.google.com>

I am trying to create a primary key constraint on a view in the following statement. However, I got an error ORA-00907: missing right parenthesis. If the CONSTRAINT clause is removed, then the view is created fine. Does anyone know how to creat Primary Key Constraint for a View? Thanks.

CREATE OR REPLACE VIEW RPT_VW_WMN (
CARD, EC_D_CODE, EC_M_CODE,
START_DATE, PR_NAME, PR_ID, ELIG, ANNUALY, MONTHLY, PENDING,  /* ORA-00907: missing right parenthesis error here */  CONSTRAINT MyView_pk PRIMARY KEY
 (CARD, EC_D_CODE, EC_M_CODE, START_DATE, PR_NAME, PR_ID ) )

AS
SELECT 'DUMMY' CARD, A.EC_D_CODE, A.EC_M_CODE,

				TO_DATE(TO_CHAR(A.EC_DATETIME,'MONTH DD YYYY'),'MM/DD/YYYY')


        - TO_CHAR(A.EC_DATETIME,'DD') +1 AS START_DATE,


				C.PR_NAME, C.PR_ID,
				COUNT(DISTINCT A.PNT_ID) AS ELIG,
				SUM(CASE WHEN A.PNT_ID IN
              (SELECT PNT FROM WOM WHERE APTDATE BETWEEN
               ADD_MONTHS(A.EC_DATETIME -
TO_CHAR(A.EC_DATETIME,'DD')+1, -12) AND
               A.EC_DATETIME - TO_CHAR(A.EC_DATETIME,'DD')+1)
            THEN 1 ELSE 0 END) AS ANNUALY,
				SUM(CASE WHEN A.PNT_ID IN
              (SELECT PNT FROM WOM WHERE APPTDATE BETWEEN
              A.EC_DATETIME - TO_CHAR(A.EC_DATETIME,'DD')+1 AND
              ADD_MONTHS(A.EC_DATETIME -
TO_CHAR(A.EC_DATETIME,'DD')+1,1))
            THEN 1 ELSE 0 END) AS MONTHLY,
				SUM(CASE WHEN A.PNT_ID NOT IN
             (SELECT PNTFROM WOM WHERE APPTDATE BETWEEN
              ADD_MONTHS(A.EC_DATETIME -
TO_CHAR(A.EC_DATETIME,'DD')+1, -12) AND
              ADD_MONTHS(A.EC_DATETIME -
TO_CHAR(A.EC_DATETIME,'DD')+1,1))
            THEN 1 ELSE 0 END) AS PENDING

FROM EC A, PR C
WHERE A.EC_DATETIME BETWEEN A.EC_DATETIME - TO_CHAR(A.EC_DATETIME,'DD')+1
      AND ADD_MONTHS(A.EC_DATETIME - TO_CHAR(A.EC_DATETIME,'DD')+1, 1) - 1
AND RTRIM(A.PR_ID_CODE) = C.PR_ID_CODE (+) AND A.APT_STATUS_ID = 1
GROUP BY A.EC_D_CODE, A.EC_M_CODE,
TO_DATE(TO_CHAR(A.EC_DATETIME,'MONTH DD YYYY'),'MM/DD/YYYY')

• TO_CHAR(A.EC_DATETIME,'DD') +1 , C.PR_NAME, C.PR_ID

/*******************************************************************************The

following is description from Oracle9i Application Developer's Guide - Fundamentals Release 1 (9.0.1) for creating a view.

*******************************************************************************/

CREATE [OR REPLACE] [[NO] FORCE] VIEW [schema .] view [ ( { alias [column_constraint [column_constraint]...]

    | table_or_view_constraint
    }
    [, { alias column_constraint [column_constraint]...

       | table_or_view_constraint
       }

    ]...
  )
| object_view_clause
]
AS subquery [subquery_restriction_clause];

table_or_view_constraint::=
[CONSTRAINT constraint]
{ UNIQUE ( column [, column]... )

| PRIMARY KEY ( column [, column]... )
| FOREIGN KEY ( column [, column]... ) references_clause
| CHECK ( condition )

}
constraint_state

constraint_state::=
[ [[NOT] DEFERRABLE] [INITIALLY { IMMEDIATE | DEFERRED }] | [INITIALLY { IMMEDIATE | DEFERRED }] [[NOT] DEFERRABLE] ]
[ RELY | NORELY ] [using_index_clause] [ ENABLE | DISABLE ] [ VALIDATE | NOVALIDATE ] [exceptions_clause] Received on Thu Oct 09 2003 - 01:00:19 CEST