sql - 我的 VIEW 语句出现 "missing right parenthesis"错误 (Oracle)

Question

我的 SQL 代码出现以下错误:

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

这是我的代码

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

)

我想基于此表创建 View :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)    

Answer 1

完全删除括号:

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;

问题是这里的ORDER BY。这也将起作用:

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;

创建 View 定义是:CREATE ... VIEW ... AS <subquery> .在哪里<subquery>具有以下语法:

如你所见ORDER BY必须在 (<subquery>) 之外