SQL Error: ORA-00904: : invalid identifier in CREATE AS SELECT

Question

I am trying to create a table from 2 other tables in Oracle SQL Developer:

CREATE TABLE share_stock(
  share_id    NUMBER(6,0), 
  share_price NUMBER(10,2),
  company_id  NUMBER(6,0), 
  company_name VARCHAR2(50), 
  ticker_symbol VARCHAR2(4),
AS SELECT share_price.share_price_id, share_price.price, share_price.company_id, company.name, company.ticker_symbol 
FROM share_price, company
WHERE share_price.company_id = company.company_id,
CONSTRAINT sh_pk PRIMARY KEY (share_price.share_price_id),
CONSTRAINT sh_pr_fk FOREIGN KEY (share_price.share_price_id) REFERENCES share_price(share_price_id)
);

Basically I am trying to perform a CREATE AS SELECT, but I am getting this error:

Error at Command Line:294 Column:28 Error report: SQL Error: ORA-00904: : invalid identifier 00904. 00000 - "%s: invalid identifier"

I have tried to correct my syntax and I have not managed to get it right so far, Any ideas would be helpful,

Thanks in advance.

I've not worked with it, but the name of the feature suggests that it derives column information from the SELECT rather than you providing column definitions. — Damien_The_Unbeliever
You cannot specify the columns like that in a CREATE TABLE .. AS. The column names and types are taken from the result of the select. — a_horse_with_no_name

Robert Robert · Accepted Answer · 2013-01-17T14:19:06

Try this way:

CREATE TABLE share_stock
AS 
SELECT SP.share_price_id as share_id , 
       SP.price as share_price, SP.company_id, C.name, 
       C.ticker_symbol 
FROM share_price SP 
join company C on SP.company_id = C.company_id;

alter table share_stock
add CONSTRAINT sh_pk PRIMARY KEY (share_id);

alter table share_stock
add CONSTRAINT sh_pr_fk FOREIGN KEY (share_id) 
REFERENCES share_price(share_price_id);

SQL Error: ORA-00904: : invalid identifier in CREATE AS SELECT

2 Answers