"missing right parenthesis" error with my VIEW statement (Oracle)

Question

I'm getting the following error with my SQL code :

Error at Command Line:50 Column:1 Error report: SQL Error: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause:
*Action:

This is my code

CREATE VIEW mytestview1 (a,b,c,d,e,f,g,h,i,j,k)
AS 
(
  SELECT *
  FROM employees
  WHERE employee_id BETWEEN 5 AND 200
  ORDER BY first_name asc

)

I want to create view based on this table : Employees

-------------- -------- ------------ 
EMPLOYEE_ID    NOT NULL NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(25) 
EMAIL          NOT NULL VARCHAR2(25) 
PHONE_NUMBER            VARCHAR2(20) 
HIRE_DATE      NOT NULL DATE         
JOB_ID         NOT NULL VARCHAR2(10) 
SALARY                  NUMBER(8,2)  
COMMISSION_PCT          NUMBER(2,2)  
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)

Multisync Multisync · Accepted Answer · 2014-11-21T18:42:45

Remove parentheses at all:

CREATE VIEW mytestview1 (a,b,c,d,e,f,g,h,i,j,k)
AS 
  SELECT *
  FROM employees
  WHERE employee_id BETWEEN 5 AND 200
  ORDER BY first_name asc;

The problem is ORDER BY here. This will also work:

CREATE VIEW mytestview1 (a,b,c,d,e,f,g,h,i,j,k)
AS (
  SELECT *
  FROM employees
  WHERE employee_id BETWEEN 5 AND 200
  )
  ORDER BY first_name asc;

The create view definition is: CREATE ... VIEW ... AS <subquery>. Where <subquery> has the following syntax:

enter image description here

As you can see ORDER BY must be outside of the (<subquery>)

"missing right parenthesis" error with my VIEW statement (Oracle)

1 Answers