insert into xscjb(XSXH,KCBH,KCMC,KCXQ,KSCS,XSCJ,CJBZ)
select XSXH,KCBH,KCMC,KCXQ,KSCS,cast(case XSCJ
when '缺席' then '0'
when '合格' then '60'
when '无效' then '0'
when '缓考' then '0'
else XSCJ
end as numeric(5,2)),
case XSCJ
when '缺席' then '缺席'
when '合格' then '合格'
when '无效' then '无效'
when '缓考' then '缓考'
else XSCJ
end
from jwccjbxscjb中XSCJ是numeric类型
jwccjb 中XSCJ 是varchar类型,因为其中有分数和汉字,
在转换时出现将数据类型 varchar 转换为 numeric 时出错。
应该怎么处理啊?
谢谢
select XSXH,KCBH,KCMC,KCXQ,KSCS,cast(case XSCJ
when '缺席' then '0'
when '合格' then '60'
when '无效' then '0'
when '缓考' then '0'
else XSCJ
end as numeric(5,2)),
case XSCJ
when '缺席' then '缺席'
when '合格' then '合格'
when '无效' then '无效'
when '缓考' then '缓考'
else XSCJ
end
from jwccjbxscjb中XSCJ是numeric类型
jwccjb 中XSCJ 是varchar类型,因为其中有分数和汉字,
在转换时出现将数据类型 varchar 转换为 numeric 时出错。
应该怎么处理啊?
谢谢
varchar类型
select XSXH,KCBH,KCMC,KCXQ,KSCS,cast(case XSCJ
when '缺席' then '0'
when '合格' then '60'
when '无效' then '0'
when '缓考' then '0'
else ltrim(XSCJ)
end as numeric(5,2)),
case XSCJ
when '缺席' then '缺席'
when '合格' then '合格'
when '无效' then '无效'
when '缓考' then '缓考'
else ltrim(XSCJ)
end
from jwccjb
when '缺席' then '0'
when '合格' then '60'
when '无效' then '0'
when '缓考' then '0'
else XSCJ
end as numeric(5,2)),
else xscj这儿出了问题,如果xscj不在上述列表中且包含非数字字符,就会出问题.
insert into xscjb(XSXH,KCBH,KCMC,KCXQ,KSCS,XSCJ,CJBZ)
select XSXH,KCBH,KCMC,KCXQ,KSCS,cast(case XSCJ
when '缺席' then '0'
when '合格' then '60'
when '无效' then '0'
when '缓考' then '0'
else 0
end as numeric(5,2)),
case XSCJ
when '缺席' then '缺席'
when '合格' then '合格'
when '无效' then '无效'
when '缓考' then '缓考'
else 0
end
from jwccjb
这么来吧
select XSXH,KCBH,KCMC,KCXQ,KSCS,(case XSCJ
when '缺席' then '0'
when '合格' then '60'
when '无效' then '0'
when '缓考' then '0'
else ltrim(XSCJ)
end),
(case XSCJ
when '缺席' then '缺席'
when '合格' then '合格'
when '无效' then '无效'
when '缓考' then '缓考'
else ltrim(XSCJ)
end)
from jwccjb
--try
insert into xscjb(XSXH,KCBH,KCMC,KCXQ,KSCS,XSCJ,CJBZ)
select XSXH,KCBH,KCMC,KCXQ,KSCS,cast(case XSCJ
when '缺席' then '0'
when '合格' then '60'
when '无效' then '0'
when '缓考' then '0'
else case when isnumeric(XSCJ)=1 then xscj else 0 end
end as numeric(5,2)),
case XSCJ
when '缺席' then '缺席'
when '合格' then '合格'
when '无效' then '无效'
when '缓考' then '缓考'
else XSCJ
end
from jwccjb
insert into xscjb(XSXH,KCBH,KCMC,KCXQ,KSCS,XSCJ,CJBZ)
select XSXH,KCBH,KCMC,KCXQ,KSCS,cast(case XSCJ
when '缺席' then '0'
when '合格' then '60'
when '无效' then '0'
when '缓考' then '0'
else '0'
end as numeric(5,2)),
case XSCJ
when '缺席' then '缺席'
when '合格' then '合格'
when '无效' then '无效'
when '缓考' then '缓考'
else XSCJ
end
from jwccjb
或者直接赋0得了insert into xscjb(XSXH,KCBH,KCMC,KCXQ,KSCS,XSCJ,CJBZ)
select XSXH,KCBH,KCMC,KCXQ,KSCS,cast(case XSCJ
when '缺席' then '0'
when '合格' then '60'
when '无效' then '0'
when '缓考' then '0'
else '0'
end as numeric(5,2)),
case XSCJ
when '缺席' then '缺席'
when '合格' then '合格'
when '无效' then '无效'
when '缓考' then '缓考'
else XSCJ
end
from jwccjb