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_DA TETIME,'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_DA TETIME,'DD')+1, -12) AND
A.EC_DATETIME - TO_CHAR(A.EC_DA TETIME,'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_DA TETIME,'DD')+1 AND
ADD_MONTHS(A.EC _DATETIME -
TO_CHAR(A.EC_DA TETIME,'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_DA TETIME,'DD')+1, -12) AND
ADD_MONTHS(A.EC _DATETIME -
TO_CHAR(A.EC_DA TETIME,'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_DA TETIME,'DD')+1
AND ADD_MONTHS(A.EC _DATETIME - TO_CHAR(A.EC_DA TETIME,'DD')+1, 1)
- 1
AND RTRIM(A.PR_ID_C ODE) = 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_DA TETIME,'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_constrai nt [column_constrai nt]...]
| table_or_view_c onstraint
}
[, { alias column_constrai nt [column_constrai nt]...
| table_or_view_c onstraint
}
]...
)
| object_view_cla use
]
AS subquery [subquery_restri ction_clause];
table_or_view_c onstraint::=
[CONSTRAINT constraint]
{ UNIQUE ( column [, column]... )
| PRIMARY KEY ( column [, column]... )
| FOREIGN KEY ( column [, column]... ) references_clau se
| CHECK ( condition )
}
constraint_stat e
constraint_stat e::=
[ [[NOT] DEFERRABLE] [INITIALLY { IMMEDIATE | DEFERRED }]
| [INITIALLY { IMMEDIATE | DEFERRED }] [[NOT] DEFERRABLE]
]
[ RELY | NORELY ] [using_index_cla use] [ ENABLE | DISABLE ]
[ VALIDATE | NOVALIDATE ] [exceptions_clau se] 1 55948
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_cla use, the exceptions_clau se
clause, or the ON DELETE clause of the references_clau se.
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********@yaho o.com (js) wrote in message news:<23******* *************** ****@posting.go ogle.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_DA TETIME,'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_DA TETIME,'DD')+1, -12) AND A.EC_DATETIME - TO_CHAR(A.EC_DA TETIME,'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_DA TETIME,'DD')+1 AND ADD_MONTHS(A.EC _DATETIME - TO_CHAR(A.EC_DA TETIME,'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_DA TETIME,'DD')+1, -12) AND ADD_MONTHS(A.EC _DATETIME - TO_CHAR(A.EC_DA TETIME,'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_DA TETIME,'DD')+1 AND ADD_MONTHS(A.EC _DATETIME - TO_CHAR(A.EC_DA TETIME,'DD')+1, 1) - 1 AND RTRIM(A.PR_ID_C ODE) = 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_DA TETIME,'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_constrai nt [column_constrai nt]...] | table_or_view_c onstraint } [, { alias column_constrai nt [column_constrai nt]... | table_or_view_c onstraint } ]... ) | object_view_cla use ] AS subquery [subquery_restri ction_clause];
table_or_view_c onstraint::= [CONSTRAINT constraint] { UNIQUE ( column [, column]... ) | PRIMARY KEY ( column [, column]... ) | FOREIGN KEY ( column [, column]... ) references_clau se | CHECK ( condition ) } constraint_stat e
constraint_stat e::= [ [[NOT] DEFERRABLE] [INITIALLY { IMMEDIATE | DEFERRED }] | [INITIALLY { IMMEDIATE | DEFERRED }] [[NOT] DEFERRABLE] ] [ RELY | NORELY ] [using_index_cla use] [ ENABLE | DISABLE ] [ VALIDATE | NOVALIDATE ] [exceptions_clau se] This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: M. Mehta |
last post by:
It seems that you can not create a materialized view if you are using
outer joins...can someone please verify this?
Thanks
M. Mehta
Please follow my example below:
created 2 tables:
|
by: Not Me |
last post by:
Hi,
Is it possible to create some SQL in VBA, 'run it' then view as a datasheet
the results?
I'm trying to throw together a fairly large search form, and VBA seems the
best way to parse the parameters.
Cheers,
Chris
|
by: sea |
last post by:
Is it a good idea to programatically create a primary key? For example
in a table called names, I have the following fields, (1) firstname
(2)lastname (3) ID
- will it be ok to create a primary key using for example the first 2
letters of the first name and the last 2 letters of the last name
AFTER the user enters the first and last names...
|
by: qfchen |
last post by:
Hi
I'm using Visual Studio 2005 for database application. the database is MS access. I need to create a view in server explorer, when I right click on view folder, I can't see "Add New View" from the context menu. What I saw is "New Query",
Anyway I just make a query from this view, but can't save it.
Anyone know how to create a view...
|
by: NithyaJai |
last post by:
Hai,
I got a doubt on creating a view?
Is that passible to create a view in MS Access 2000.......
if so plz help me to do it...
Thanks....
| |
by: WayneW |
last post by:
I need to create a view of tables in 2 databases. Is this possible?
|
by: rupinder03 |
last post by:
I want to create tree view in data grid in vb.net 2005.can anyone tell me hw to create it................its urgent.......plz reply soon..............
|
by: js |
last post by:
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,...
|
by: kensushi |
last post by:
Hi,
Im somewhat new to oracle SP writing. I need to do the following from the SP
1. execute the query
2. create a view and populate with the result of the above query
3 query that view and take the result set to the application
your comments are much appreciated.
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |