CREATE TABLE XSCJB(
CJBH NUMBER(10) NOT NULL,
XSXH VARCHAR2(15),
KCBH VARCHAR2(15),
KCMC VARCHAR2(50) NOT NULL,
KCXQ VARCHAR2(12) NOT NULL,
KSCS INT,
XSCJ NUMBER(5,2) NOT NULL CHECK(XSCJ BETWEEN 0 AND 100),
JSBH VARCHAR2(10),
JSXM VARCHAR2(20),
CJBZ VARCHAR2(20),
PRIMARY KEY(XSXH,KCBH,KSCS),
FOREIGN KEY (XSXH) REFERENCES XSXXB(XSXH)
);create table xs(
XSXH VARCHAR2(15),
KCBH VARCHAR2(15),
KCMC VARCHAR2(50),
KCXQ VARCHAR2(12) ,
KSCS INT,
XSCJ varchar2(10),
JSBH VARCHAR2(10),
JSXM VARCHAR2(20)
);
SQL> insert into XSCJB(
2 CJBH,XSXH,KCBH,KCMC,KCXQ,KSCS,XSCJ,JSBH,JSXM,CJBZ
3 )
4 SELECT rownum R,XSXH,KCBH,KCMC,KCXQ,KSCS,
5 (CASE WHEN xsCJ='缺考' OR xscj='无效' then 0
6 when xsCJ='合格' then 60 ELSE (to_number(xsCJ)) end),
7 JSBH,JSXM,xsCJ from
8 xs;
when xsCJ='合格' then 60 ELSE (to_number(xsCJ)) end),
*
第 6 行出现错误:
ORA-01722: 无效数字
是为什么呀,但执行:
SELECT rownum R,XSXH,KCBH,KCMC,KCXQ,KSCS,
(CASE WHEN xsCJ='缺考' OR xscj='无效' then 0
when xsCJ='合格' then 60 ELSE to_number(xsCJ) end),
JSBH,JSXM,xsCJ from
xs;
却可以成功.
CJBH NUMBER(10) NOT NULL,
XSXH VARCHAR2(15),
KCBH VARCHAR2(15),
KCMC VARCHAR2(50) NOT NULL,
KCXQ VARCHAR2(12) NOT NULL,
KSCS INT,
XSCJ NUMBER(5,2) NOT NULL CHECK(XSCJ BETWEEN 0 AND 100),
JSBH VARCHAR2(10),
JSXM VARCHAR2(20),
CJBZ VARCHAR2(20),
PRIMARY KEY(XSXH,KCBH,KSCS),
FOREIGN KEY (XSXH) REFERENCES XSXXB(XSXH)
);create table xs(
XSXH VARCHAR2(15),
KCBH VARCHAR2(15),
KCMC VARCHAR2(50),
KCXQ VARCHAR2(12) ,
KSCS INT,
XSCJ varchar2(10),
JSBH VARCHAR2(10),
JSXM VARCHAR2(20)
);
SQL> insert into XSCJB(
2 CJBH,XSXH,KCBH,KCMC,KCXQ,KSCS,XSCJ,JSBH,JSXM,CJBZ
3 )
4 SELECT rownum R,XSXH,KCBH,KCMC,KCXQ,KSCS,
5 (CASE WHEN xsCJ='缺考' OR xscj='无效' then 0
6 when xsCJ='合格' then 60 ELSE (to_number(xsCJ)) end),
7 JSBH,JSXM,xsCJ from
8 xs;
when xsCJ='合格' then 60 ELSE (to_number(xsCJ)) end),
*
第 6 行出现错误:
ORA-01722: 无效数字
是为什么呀,但执行:
SELECT rownum R,XSXH,KCBH,KCMC,KCXQ,KSCS,
(CASE WHEN xsCJ='缺考' OR xscj='无效' then 0
when xsCJ='合格' then 60 ELSE to_number(xsCJ) end),
JSBH,JSXM,xsCJ from
xs;
却可以成功.
上面这位哥,具体要怎么改呀
select xsCJ
from xs
group by xsCJ估计是除了你列出来的
'缺考' '无效' '合格'
还有不是数字的
我查出来了,果然里面还有缓考等不是数字的字符,这是我们实习的作业,里面有20000多条数据,老师只说:在导入时需按以下等级进行转换: 合格——60,缺考——0,无效——0
没提还有其它不是数字的字符,我就认为没其它的了.