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

oracle, sql, view

Solution

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:

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

Problem

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) ```

Original source