SQL Create Table with PK

[kawi6rr]

When I run this code to create a table with a PK I keep getting this error on the line where I have my contraint. Adding another parenthesis does not help.

SQL Error: ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"

Any help is appreciated, Thanks!!

CREATE TABLE QM_CATARACTS(
CAT_ID NUMBER (10),
PAT_ID CHAR (15),
PAT_ENC_CSN_ID CHAR (15),
CONTACT_DATE DATE,
ENC_TYPE_C CHAR (10),
PCP_PROV_ID CHAR (15),
VISIT_PROV_ID CHAR (15),
VISIT_PROV_TITLE CHAR (10),
DEPARTMENT_ID CHAR (15),
ENC_CLOSED_YN VARCHAR (2),
ENC_CLOSED_USER_ID VARCHAR (15),
ENC_CLOSE_DATE DATE,
PROV_NAME VARCHAR(255),
PROV_TYPE VARCHAR(50),
DEPARTMENT_NAME VARCHAR(255),
SPECIALTY VARCHAR(50),
IDENTITY_ID CHAR (15),
LINE CHAR(2),
ICD9_CODE VARCHAR(10),
DX_NAME VARCHAR(255),
NAME VARCHAR(50)
CONSTRAINT QM_CATARACTS_PK PRIMARY KEY (CAT_ID))
;

 

[GohDiamond]

SYNTAX Issues:
1) Numbers don't have Character space definitions so you can't 'Number (10)'
2) You dropped a comma

The following works:

CREATE TABLE QM_CATARACTS
(
CAT_ID NUMBER,
PAT_ID CHAR (15),
PAT_ENC_CSN_ID CHAR (15),
CONTACT_DATE DATE,
ENC_TYPE_C CHAR (10),
PCP_PROV_ID CHAR (15),
VISIT_PROV_ID CHAR (15),
VISIT_PROV_TITLE CHAR (10),
DEPARTMENT_ID CHAR (15),
ENC_CLOSED_YN VARCHAR (2),
ENC_CLOSED_USER_ID VARCHAR (15),
ENC_CLOSE_DATE DATE,
PROV_NAME VARCHAR(255),
PROV_TYPE VARCHAR(50),
DEPARTMENT_NAME VARCHAR(255),
SPECIALTY VARCHAR(50),
IDENTITY_ID CHAR (15),
LINE CHAR(2),
ICD9_CODE VARCHAR(10),
DX_NAME VARCHAR(255),
NAME VARCHAR(50),
CONSTRAINT QM_CATARACTS_PK PRIMARY KEY (CAT_ID)
)

Question: Why are you using a Number format (other than Autonumber) for a field which isn't used for calculation? use text instead

CAT_ID CHAR(10),

Cheers!
Goh

 

[kawi6rr]

I'm trying to make that field an auto number but haven't been able to figure it out. How do I make it an auto number?

Other then that thanks for your help, very appreciated.

 

[jdraw]

I'm not sure of what you are doing. This is a Microsoft Access forum, but you're getting an Oracle error message??? Please explain the environment.

 

[kawi6rr]

It's an Oracle environment, a data warehouse, I'm just trying to make a table with a auto number primary key. Sorry I'll try to post this on a SQL specific forum.

Thanks for your help though.

 

[GohDiamond]

JDraw I thought he was using Access FE against an Oracle BE, anyways... here's the answer to the autonumber question:

Change
CAT_ID NUMBER,
to
CAT_ID AUTOINCREMENT,

I found this post interesting because I'm in the middle of doing something similar, so I thought I'd indulge myself in a little investigation to see if I could make it work in Access. The research I put into it has helped me as much as my answer may have helped kawi6rr...

Thanks for asking anyway
Cheers!
Goh

 

[jdraw]

He may be using Access FE. If he's building an autonumber field in an Access table, it would be in Access and the data type (at least it used to be) IDENTITY.-- in Oracle he would be using a sequence. It wasn't and still isn't clear to me where he's building the Table. But the Oracle error message tells me it's Oracle. Perhaps he wants a table in Access, and until he tells us clearly we're only guessing.

Good luck with your project.