mysql5资源共享

 找回密码
 立即注册
楼主: phillip629

[Oracle] 【开发】ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 CHAR\nORA-06512

[复制链接]

0

主题

37

帖子

82

积分

注册会员

Rank: 2

积分
82
发表于 2019-7-10 14:36:52 | 显示全部楼层
试了一下
-CAST(a.CurrOccupationRatio-a.qgOccupationRatioASvarchar(100))--number

+CAST(a.CurrOccupationRatio-a.qgOccupationRatioASvarchar(100))--char
等于
CAST(a.CurrOccupationRatio-a.qgOccupationRatioASvarchar(100))--char

加号并没有用,如果你要加号的话,''+''要两个单引号,且上面的类型全要改成字符型
回复

使用道具 举报

2

主题

15

帖子

34

积分

新手上路

Rank: 1

积分
34
 楼主| 发表于 2019-7-10 15:24:47 | 显示全部楼层
谢谢大家,上个问题解决了,还是这段代码又出现了新的问题,ORA-00905:缺失关键字\nORA-06512: 大佬们帮忙看下啊 strsql:='createtableresultasSELECTa.OrgCode,a.OrgName,a.NumberOfPeople,a.MedNum,a.CurrOccupationRatio ,CASE WHEN(a.CurrOccupationRatio-a.LastOccupationRatio)<0THEN'||to_char('-')||'CAST((a.LastOccupationRatio-a.CurrOccupationRatio)ASvarchar2(100)) WHEN(a.CurrOccupationRatio-a.LastOccupationRatio)=0THEN0.00 ELSE'||'+'||'CAST((a.CurrOccupationRatio-a.LastOccupationRatio)ASvarchar2(100))ASvarchar2(100) ENDASYearIncrease ,CASE WHEN(a.CurrOccupationRatio-a.qgOccupationRatio)<0THEN'||to_char('-')||'CAST((a.qgOccupationRatio-a.CurrOccupationRatio)ASvarchar2(100)) WHEN(a.CurrOccupationRatio-a.qgOccupationRatio)=0THEN0.00 ELSE'||'+'||'CAST((a.CurrOccupationRatio-a.qgOccupationRatio)ASvarchar2(100))ASvarchar2(100) ENDASQgIncrease FROM( SELECTa.OrgCode,a.OrgName,a.NumberOfPeople ,( SELECTCOUNT(1) FROMtempb WHEREto_char(a.orgName)=b.orgName )ASMedNum ,CASE WHENnvl(a.numberofpeople,CAST(0ASnumber))=0THENCAST(0ASnumber) ELSECAST(( SELECTCOUNT(1) FROMtempb WHEREto_char(a.orgName)=b.orgName )/(CAST(a.numberofpeopleASdecimal)/1000)*100ASdecimal(18,2)) ENDASCurrOccupationRatio ,CASE WHENnvl(a.numberofpeople,CAST(0ASnumber))=0THENCAST(0ASnumber) ELSECAST(( SELECTCOUNT(1) FROMtemp1b WHEREto_char(a.orgName)=b.orgName )/(CAST(a.numberofpeopleASdecimal)/1000)*100ASdecimal(18,2)) ENDASLastOccupationRatio,CAST(( SELECTCOUNT(1) FROMjg_infob WHEREisfz=1 ANDto_char(b.passdate,''yyyy'')='||yearr||' )/(CAST(( SELECTSUM(numberofpeople) FROMKgOrgswhereLV=1 )ASdecimal)/1000)*100ASdecimal(18,2))ASqgOccupationRatio FROMtempOrgsa )a'; executeimmediatestrsql;
回复

使用道具 举报

0

主题

37

帖子

82

积分

注册会员

Rank: 2

积分
82
发表于 2019-7-10 15:44:40 | 显示全部楼层
你把executeimmediatestrsql;
改成dbms_output.put_line(strsql);
拿到sql执行下就知道问题了
回复

使用道具 举报

2

主题

15

帖子

34

积分

新手上路

Rank: 1

积分
34
 楼主| 发表于 2019-7-10 17:02:57 | 显示全部楼层
执行了,报ora00900,辛苦大家了,我先结贴了
回复

使用道具 举报

0

主题

37

帖子

82

积分

注册会员

Rank: 2

积分
82
发表于 2019-7-10 17:13:17 | 显示全部楼层
你casewhen那里是想要字符(例如显示+1)还是数字?
回复

使用道具 举报

2

主题

15

帖子

34

积分

新手上路

Rank: 1

积分
34
 楼主| 发表于 2019-7-10 17:16:54 | 显示全部楼层
createtableresulttasSELECTa.OrgCode,a.OrgName,a.NumberOfPeople,a.MedNum,a.CurrOccupationRatio ,CASE WHENa.CurrOccupationRatio-a.LastOccupationRatio<0THEN'||to_char('-')||'CAST((a.LastOccupationRatio-a.CurrOccupationRatio)ASvarchar2(100)) WHENa.CurrOccupationRatio-a.LastOccupationRatio=0THEN0.00 ELSE'||'+'||'CAST((a.CurrOccupationRatio-a.LastOccupationRatio)ASvarchar2(100))ASvarchar2(100) ENDASYearIncrease ,CASE WHENa.CurrOccupationRatio-a.qgOccupationRatio<0THEN'||to_char('-')||'CAST(a.qgOccupationRatio-a.CurrOccupationRatioASvarchar2(100)) WHENa.CurrOccupationRatio-a.qgOccupationRatio=0THEN0.00 ELSE'||'+'||'CAST(a.CurrOccupationRatio-a.qgOccupationRatioASvarchar2(100))ASvarchar2(100) ENDASQgIncrease 这部分要字符,数据类型不匹配问题已经解决了,现在报ORA-00905:缺失关键字
回复

使用道具 举报

2

主题

15

帖子

34

积分

新手上路

Rank: 1

积分
34
 楼主| 发表于 2019-7-10 17:19:32 | 显示全部楼层
不加'||to_char('-')||'也报ORA-00905:缺失关键字
回复

使用道具 举报

0

主题

37

帖子

82

积分

注册会员

Rank: 2

积分
82
发表于 2019-7-10 17:26:16 | 显示全部楼层
strsql:='createtableresultasSELECTa.OrgCode,a.OrgName,a.NumberOfPeople,a.MedNum,a.CurrOccupationRatio ,CASE WHEN(a.CurrOccupationRatio-a.LastOccupationRatio)<0THEN''-''||CAST((a.LastOccupationRatio-a.CurrOccupationRatio)ASvarchar2(100)) WHEN(a.CurrOccupationRatio-a.LastOccupationRatio)=0THEN''0.00'' ELSE''+''||CAST((a.CurrOccupationRatio-a.LastOccupationRatio)ASvarchar2(100)) ENDASYearIncrease ,CASE WHEN(a.CurrOccupationRatio-a.qgOccupationRatio)<0THEN''-''||CAST((a.qgOccupationRatio-a.CurrOccupationRatio)ASvarchar2(100)) WHEN(a.CurrOccupationRatio-a.qgOccupationRatio)=0THEN''0.00'' ELSE''+''||CAST((a.CurrOccupationRatio-a.qgOccupationRatio)ASvarchar2(100)) ENDASQgIncrease FROM( SELECTa.OrgCode,a.OrgName,a.NumberOfPeople ,( SELECTCOUNT(1) FROMtempb WHEREto_char(a.orgName)=b.orgName )ASMedNum ,CASE WHENnvl(a.numberofpeople,CAST(0ASnumber))=0THENCAST(0ASnumber) ELSECAST(( SELECTCOUNT(1) FROMtempb WHEREto_char(a.orgName)=b.orgName )/(CAST(a.numberofpeopleASdecimal)/1000)*100ASdecimal(18,2)) ENDASCurrOccupationRatio ,CASE WHENnvl(a.numberofpeople,CAST(0ASnumber))=0THENCAST(0ASnumber) ELSECAST(( SELECTCOUNT(1) FROMtemp1b WHEREto_char(a.orgName)=b.orgName )/(CAST(a.numberofpeopleASdecimal)/1000)*100ASdecimal(18,2)) ENDASLastOccupationRatio,CAST(( SELECTCOUNT(1) FROMjg_infob WHEREisfz=1 ANDto_char(b.passdate,''yyyy'')='||yearr||' )/(CAST(( SELECTSUM(numberofpeople) FROMKgOrgswhereLV=1 )ASdecimal)/1000)*100ASdecimal(18,2))ASqgOccupationRatio FROMtempOrgsa )a';
回复

使用道具 举报

0

主题

37

帖子

82

积分

注册会员

Rank: 2

积分
82
发表于 2019-7-10 17:34:00 | 显示全部楼层
执行后报错,光标会停在报错的附近位置,你就知道那里可能有错了
回复

使用道具 举报

0

主题

6

帖子

22

积分

新手上路

Rank: 1

积分
22
发表于 2019-7-11 08:19:41 | 显示全部楼层
casewhen后的then的类型不一致,一个是varchar2,一个是number,应该是varchar2的'0.00'
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|mysql5.com Inc. ( 闽ICP备17002856号-1 )

GMT+8, 2019-10-22 06:50 , Processed in 0.050115 second(s), 16 queries .

Powered by MySQL5.com X3.4

© 2003-2019 MySQL5.com.

快速回复 返回顶部 返回列表