Oracle ORA-00936: missing expression 错误

qq_30104843 2017-08-16 11:55:18

Oracle中，我的查询语句如下：
SELECT
a.id AS "id",
a.id_card AS "idCard",
a.sex AS "sex",
a.nation AS "nation",
a.origin_place AS "originPlace",
a.age AS "age",
a.household_attribute AS "householdAttribute",
a.induction_time AS "inductionTime",
a.birthdate AS "birthdate",
a.graduation_time AS "graduationTime",
a.political_outlook AS "politicalOutlook",
a.graduation_school AS "graduationSchool",
a.major AS "major",
a.max_education AS "maxEducation",
a.working_years AS "workingYears",
a.marital_status AS "maritalStatus",
a.fertility_status AS "fertilityStatus",
a.home_address AS "homeAddress",
a.bankcard_number AS "bankcardNumber",
a.opening_bank AS "openingBank",
a.emergency_contact AS "emergencyContact",
a.emergency_contact_phone AS "emergencyContactPhone",
FROM
SYS_USER_COMMON_INFORATION a

报错：[Err] ORA-00936: missing expression

这张的表建表语句是：
CREATE TABLE "USUM"."NewTable" (
"id" VARCHAR2(255 BYTE) NOT NULL ,
"id_card" NUMBER(20) NOT NULL ,
"sex" VARCHAR2(255 BYTE) NULL ,
"nation" VARCHAR2(255 BYTE) NULL ,
"origin_place" VARCHAR2(255 BYTE) NULL ,
"age" NUMBER(20) NULL ,
"household_attribute" VARCHAR2(255 BYTE) NULL ,
"induction_time" DATE NULL ,
"birthdate" DATE NULL ,
"graduation_time" DATE NULL ,
"political_outlook" VARCHAR2(255 BYTE) NULL ,
"graduation_school" VARCHAR2(255 BYTE) NULL ,
"major" VARCHAR2(255 BYTE) NULL ,
"max_education" VARCHAR2(255 BYTE) NULL ,
"working_years" VARCHAR2(255 BYTE) NULL ,
"marital_status" VARCHAR2(255 BYTE) NULL ,
"fertility_status" VARCHAR2(255 BYTE) NULL ,
"home_address" VARCHAR2(255 BYTE) NULL ,
"bankcard_number" NUMBER(20) NULL ,
"opening_bank" VARCHAR2(255 BYTE) NULL ,
"emergency_contact" VARCHAR2(255 BYTE) NULL ,
"emergency_contact_phone" NUMBER(20) NULL ,
PRIMARY KEY ("id_card")
)
PCTFREE 10
INITRANS 1
STORAGE (
BUFFER_POOL DEFAULT
)

TABLESPACE "USERS"
LOGGING
NOCOMPRESS
NOCACHE
;

ALTER TABLE "USUM"."NewTable" ADD FOREIGN KEY ("id") REFERENCES "USUM"."SYS_USER" ("ID") ON DELETE CASCADE;

ALTER TABLE "USUM"."NewTable" ADD CHECK ("id" IS NOT NULL);

ALTER TABLE "USUM"."NewTable" ADD CHECK ("id_card" IS NOT NULL);

...全文

2514 1 打赏收藏转发到动态举报

写回复

1 条回复

切换为时间正序

请发表友善的回复…

发表回复

卖水果的net 2017-08-17

打赏
举报

回复

a.emergency_contact_phone AS "emergencyContactPhone", -- 这里的逗号要去掉 FROM SYS_USER_COMMON_INFORATION a 表名称也不对，建表语句中是 NewTable，这里写的是 sys… 建议把建表语句语句中的表名称、列名称两侧的双引号全去掉，不然以后所有的操作，你必须加上双引号。

SQL> SQL> --1 SQL> SQL> desc dept; Name Null? Type ----------------------------------------- -------- ---------------------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) SQL> select dname from dept; DNAME -------------- ACCOUNTING RESEARCH SALES OPERATIONS SQL> SQL> SQL> --1.2 SQL> SQL> desc emp; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> select ename as "姓名" ,sal+comm 年收入 from emp; select ename as "姓名" ,sal+comm 年收入 from emp * ERROR at line 1: ORA-00911: invalid character SQL> select ename as "姓名" ,sal+comm "年收入" from emp; ?? ?? ?????? ---------- ---------- SMITH ALLEN 1900 WARD 1750 JONES MARTIN 2650 BLAKE CLARK SCOTT KING TURNER 1500 ADAMS ?? ?? ?????? ---------- ---------- JAMES FORD MILLER 14 rows selected. SQL> select ename as "emp name" ,sal+comm income from emp; emp name INCOME ---------- ---------- SMITH ALLEN 1900 WARD 1750 JONES MARTIN 2650 BLAKE CLARK SCOTT KING TURNER 1500 ADAMS emp name INCOME ---------- ---------- JAMES FORD MILLER 14 rows selected. SQL> select ename ,sal,comm from emp; ENAME SAL COMM ---------- ---------- ---------- SMITH 800 ALLEN 1600 300 WARD 1250 500 JONES 2975 MARTIN 1250 1400 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 0 ADAMS 1100 ENAME SAL COMM ---------- ---------- ---------- JAMES 950 FORD 3000 MILLER 1300 14 rows selected. SQL> select ename as "emp name" ,sal+nvl(comm,0) income from emp;//nvl是oracle中的函数意思是如果comm为null，那么返回0，否则返回comm的值 emp name INCOME ---------- ---------- SMITH 800 ALLEN 1900 WARD 1750 JONES 2975 MARTIN 2650 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100 emp name INCOME ---------- ---------- JAMES 950 FORD 3000 MILLER 1300 14 rows selected. SQL> select ename as "emp name" ,12 *(sal+nvl(comm,0) ) income from emp; emp name INCOME ---------- ---------- SMITH 9600 ALLEN 22800 WARD 21000 JONES 35700 MARTIN 31800 BLAKE 34200 CLARK 29400 SCOTT 36000 KING 60000 TURNER 18000 ADAMS 13200 emp name INCOME ---------- ---------- JAMES 11400 FORD 36000 MILLER 15600 14 rows selected. SQL> SQL> select deptno from dept; DEPTNO ---------- 10 20 30 40 SQL> --1.3 SQL> select deptno from emp; DEPTNO ---------- 20 30 30 20 30 30 10 20 10 30 20 DEPTNO ---------- 30 20 10 14 rows selected. SQL> select distinct deptno from emp; DEPTNO ---------- 30 20 10 SQL> SQL> SQL> SQL> --2 SQL> SQL> --2.1 SQL> SQL> select ename,sal from emp where sal >2850; ENAME SAL ---------- ---------- JONES 2975 SCOTT 3000 KING 5000 FORD 3000 SQL> SQL> SQL> --2.2 SQL> SQL> SQL> select ename,sal from emp where sal<1500 or sal>2850; ENAME SAL ---------- ---------- SMITH 800 WARD 1250 JONES 2975 MARTIN 1250 SCOTT 3000 KING 5000 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 10 rows selected. SQL> SQL> SQL> select ename,sal from emp where not between 1500 and 2850; select ename,sal from emp where not between 1500 and 2850 * ERROR at line 1: ORA-00936: missing expression SQL> select ename,sal from emp where sal not between 1500 and 2850; ENAME SAL ---------- ---------- SMITH 800 WARD 1250 JONES 2975 MARTIN 1250 SCOTT 3000 KING 5000 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 10 rows selected. SQL> select ename,sal from emp where sal between 1500 and 2850; ENAME SAL ---------- ---------- ALLEN 1600 BLAKE 2850 CLARK 2450 TURNER 1500 SQL> SQL> SQL> SQL> SQL> --2.3 SQL> SQL> select ename,deptno from emp where empno=7566; ENAME DEPTNO ---------- ---------- JONES 20 SQL> SQL> --2.4 SQL> SQL> select ename,sal from emp where sal>1500 and (deptno=10 or deptno=30); ENAME SAL ---------- ---------- ALLEN 1600 BLAKE 2850 CLARK 2450 KING 5000 SQL> SQL> select ename,sal from emp where sal>1500 and deptno in(10,30); ENAME SAL ---------- ---------- ALLEN 1600 BLAKE 2850 CLARK 2450 KING 5000 SQL> SQL> SQL> --2.5 SQL> SQL> desc emp; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> select empno,ename,job,mgr from emp; EMPNO ENAME JOB MGR ---------- ---------- --------- ---------- 7369 SMITH CLERK 7902 7499 ALLEN SALESMAN 7698 7521 WARD SALESMAN 7698 7566 JONES MANAGER 7839 7654 MARTIN SALESMAN 7698 7698 BLAKE MANAGER 7839 7782 CLARK MANAGER 7839 7788 SCOTT ANALYST 7566 7839 KING PRESIDENT 7844 TURNER SALESMAN 7698 7876 ADAMS CLERK 7788 EMPNO ENAME JOB MGR ---------- ---------- --------- ---------- 7900 JAMES CLERK 7698 7902 FORD ANALYST 7566 7934 MILLER CLERK 7782 14 rows selected. SQL> SQL> SQL> select ename,job from emp where mgr=''; no rows selected SQL> select ename,job from emp where mgr is null; ENAME JOB ---------- --------- KING PRESIDENT SQL> select ename,job from emp where mgr is not null; ENAME JOB ---------- --------- SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST TURNER SALESMAN ADAMS CLERK JAMES CLERK ENAME JOB ---------- --------- FORD ANALYST MILLER CLERK 13 rows selected. SQL> spool off

oracle ORA-00936 missing expression 报错的几种情况和解决方法

oracle中ORA-00936: 缺失表达式 oracle ORA 00936 missing expression 解决方法

oracle ORA-00936: missing expression 报错的几种情况和解决方法 2013-04-29 17:20:38 分类： Oracle ORA-00936: missing expression Cause: A required part of a clause or expression has been omitted. For

一般情况下出现“> ORA-00936: missing expression ” 无非是这几种原因：选取的最后一个字段与from之间有逗号 SQL语句中有表字段是oracle关键字语句之间缺少逗号等分隔符查询语句中写了where却缺少必要条件字段定义。。。。。今天遇到问题不是上面的情况，导致的原因是“LanguageLevel ,Educational,[Appli...

17,086

社区成员

55,238

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章