Create Primary Key on View

[js]

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]

[Mauro]

Hi...
there are some restrictions on view constraints; "SQL Reference 9.2"
mentions:

/*
View Constraints
Oracle does not enforce view constraints. However, operations on views
are subject to the integrity constraints defined on the underlying
base tables. This means that you can enforce constraints on views
through constraints on base tables.

Restrictions on View Constraints
View constraints are a subset of table constraints and are subject to
the following restrictions:

You can specify only unique, primary key, and foreign key constraints
on views. However, you can define the view using the WITH CHECK OPTION
clause, which is equivalent to specifying a check constraint for the
view.
Because view constraints are not enforced directly, you cannot specify
INITIALLY DEFERRED or DEFERRABLE.
View constraints are supported only in DISABLE NOVALIDATE mode. You
must specify the keywords DISABLE NOVALIDATE when you declare the view
constraint, and you cannot specify any other mode.
You cannot specify the using_index_clause, the exceptions_clause
clause, or the ON DELETE clause of the references_clause.
You cannot define view constraints on attributes of an object column.
*/

So, you can try using this statement:

CREATE OR REPLACE VIEW RPT_VW_WMN (
CARD, EC_D_CODE, EC_M_CODE, START_DATE, PR_NAME, PR_ID, ELIG,
ANNUALY, MONTHLY, PENDING,
CONSTRAINT MyView_pk PRIMARY KEY
(CARD, EC_D_CODE, EC_M_CODE, START_DATE, PR_NAME, PR_ID) DISABLE
NOVALIDATE
)
AS
SELECT 'DUMMY' CARD, A.EC_D_CODE, A.EC_M_CODE,
etc...

Bye.

an********@yahoo.com (js) wrote in message news:<23**************************@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]