17,086
社区成员
发帖
与我相关
我的任务
分享ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 CHAR\nORA-06512
strsql:='create table result as SELECT a.OrgCode,a.OrgName, a.NumberOfPeople, a.MedNum, a.CurrOccupationRatio
, CASE
WHEN a.CurrOccupationRatio - a.LastOccupationRatio < 0 THEN '||'-'||' CAST(a.LastOccupationRatio - a.CurrOccupationRatio AS varchar(100))
WHEN a.CurrOccupationRatio - a.LastOccupationRatio = 0 THEN 0.00
ELSE '||'+'||' CAST(a.CurrOccupationRatio - a.LastOccupationRatio AS varchar(100))
END AS YearIncrease
, CASE
WHEN a.CurrOccupationRatio - a.qgOccupationRatio < 0 THEN '||'-'||' CAST(a.qgOccupationRatio - a.CurrOccupationRatio AS varchar(100))
WHEN a.CurrOccupationRatio - a.qgOccupationRatio = 0 THEN 0.00
ELSE '||'+'||' CAST(a.CurrOccupationRatio - a.qgOccupationRatio AS varchar(100))
END AS QgIncrease
FROM (
SELECT a.OrgCode,a.OrgName, a.NumberOfPeople
, (
SELECT COUNT(1)
FROM temp b
WHERE a.orgName=b.orgName
) AS MedNum
, CASE
WHEN nvl(a.numberofpeople, CAST(0 AS number)) = 0 THEN CAST(0 AS decimal(18, 2))
ELSE CAST((
SELECT COUNT(1)
FROM temp b
WHERE a.orgName=b.orgName
) / (CAST(a.numberofpeople AS decimal) / 1000) * 100 AS decimal(18, 2))
END AS CurrOccupationRatio
, CASE
WHEN nvl(a.numberofpeople, CAST(0 AS number)) = 0 THEN CAST(0 AS decimal(18, 2))
ELSE CAST((
SELECT COUNT(1)
FROM temp1 b
WHERE a.orgName=b.orgName
) / (CAST(a.numberofpeople AS decimal) / 1000) * 100 AS decimal(18, 2))
END AS LastOccupationRatio, CAST((
SELECT COUNT(1)
FROM jg_info b
WHERE isfz = 1
AND to_char(b.passdate,''yyyy'')='||yearr||'
) / (CAST((
SELECT SUM(numberofpeople)
FROM KgOrgs where LV=1
) AS decimal) / 1000) * 100 AS decimal(18, 2)) AS qgOccupationRatio
FROM tempOrgs a
) a ';
execute immediate strsql;
, CASE
WHEN a.CurrOccupationRatio - a.LastOccupationRatio < 0 THEN '||'-'||' CAST(a.LastOccupationRatio - a.CurrOccupationRatio AS varchar(100))
WHEN a.CurrOccupationRatio - a.LastOccupationRatio = 0 THEN 0.00
ELSE '||'+'||' CAST(a.CurrOccupationRatio - a.LastOccupationRatio AS varchar(100))
END AS YearIncrease
, CASE
WHEN a.CurrOccupationRatio - a.qgOccupationRatio < 0 THEN '||'-'||' CAST(a.qgOccupationRatio - a.CurrOccupationRatio AS varchar(100))
WHEN a.CurrOccupationRatio - a.qgOccupationRatio = 0 THEN 0.00
ELSE '||'+'||' CAST(a.CurrOccupationRatio - a.qgOccupationRatio AS varchar(100))
END AS QgIncrease
FROM (
SELECT a.OrgCode,a.OrgName, a.NumberOfPeople
, (
SELECT COUNT(1)
FROM temp b
WHERE a.orgName=b.orgName
) AS MedNum
, CASE
WHEN nvl(a.numberofpeople, CAST(0 AS number)) = 0 THEN CAST(0 AS decimal(18, 2))
ELSE CAST((
SELECT COUNT(1)
FROM temp b
WHERE a.orgName=b.orgName
) / (CAST(a.numberofpeople AS decimal) / 1000) * 100 AS decimal(18, 2))
END AS CurrOccupationRatio
, CASE
WHEN nvl(a.numberofpeople, CAST(0 AS number)) = 0 THEN CAST(0 AS decimal(18, 2))
ELSE CAST((
SELECT COUNT(1)
FROM temp1 b
WHERE a.orgName=b.orgName
) / (CAST(a.numberofpeople AS decimal) / 1000) * 100 AS decimal(18, 2))
END AS LastOccupationRatio, CAST((
SELECT COUNT(1)
FROM jg_info b
WHERE isfz = 1
AND to_char(b.passdate,''yyyy'')='||yearr||'
) / (CAST((
SELECT SUM(numberofpeople)
FROM KgOrgs where LV=1
) AS decimal) / 1000) * 100 AS decimal(18, 2)) AS qgOccupationRatio
FROM tempOrgs a
) a ';
execute immediate strsql;
...全文
请发表友善的回复…
发表回复
就等你一会儿 2019-07-12
- 打赏
- 举报
复制加积分，不好意思（把相关的表结构贴上来，要 create table 形式的，方便大家帮你调试。）
kiss阳光 2019-07-11
- 打赏
- 举报
case when 后的then 的类型不一致，一个是varchar2，一个是number，应该是varchar2 的'0.00'
phillip629 2019-07-11
- 打赏
- 举报
谢谢了啊，问题已近解决了
qq274786217 2019-07-11
- 打赏
- 举报
这么多代码你贴出来大家给你找也很难受。你可以把有问题的sql分几部分注释。然后再分下来几次。就能精准定位了，在具体分析。
phillip629 2019-07-10
- 打赏
- 举报
CREATE TABLE jg_info (
unitguid NVARCHAR2 ( 16 ) NOT NULL,
unitname NVARCHAR2 ( 100 ) NOT NULL,
addr NVARCHAR2 ( 200 ) NOT NULL,
postcode NVARCHAR2 ( 6 ) NULL,
c_syzxs NVARCHAR2 ( 10 ) NOT NULL,
c_jglb NVARCHAR2 ( 10 ) NOT NULL,
c_jgjb NVARCHAR2 ( 10 ) NOT NULL,
c_jgdc NVARCHAR2 ( 10 ) NOT NULL,
naturebusi NVARCHAR2 ( 10 ) NOT NULL,
c_fwdx NVARCHAR2 ( 10 ) NOT NULL,
c_cws NVARCHAR2 ( 10 ) NOT NULL,
c_yys NVARCHAR2 ( 10 ) NULL,
c_zczj NVARCHAR2 ( 20 ) NOT NULL,
fridname NVARCHAR2 ( 50 ) NOT NULL,
fridcard NVARCHAR2 ( 20 ) NOT NULL,
fzname NVARCHAR2 ( 50 ) NOT NULL,
fzridcard NVARCHAR2 ( 20 ) NOT NULL,
datevalidbegin DATE NOT NULL,
datevaliend DATE NOT NULL,
unitregcode NVARCHAR2 ( 100 ) NOT NULL,
organid NVARCHAR2 ( 128 ) NULL,
organnme NVARCHAR2 ( 50 ) NOT NULL,
passdate DATE NULL,
subject_name CLOB NOT NULL,
subjectcode CLOB NULL,
certificate NVARCHAR2 ( 500 ) NULL,
private NVARCHAR2 ( 128 ) NULL,
province NVARCHAR2 ( 50 ) NOT NULL,
provincecode NVARCHAR2 ( 50 ) NULL,
comm CLOB NULL,
rsvd CLOB NULL,
isbg NVARCHAR2 ( 2 ) NOT NULL,
isqz NVARCHAR2 ( 2 ) NOT NULL,
isfz NVARCHAR2 ( 2 ) NOT NULL,
provinceOrgName NVARCHAR2 ( 50 ) NULL,
city NVARCHAR2 ( 50 ) NULL,
county NVARCHAR2 ( 50 ) NULL,
CONSTRAINT jg_info_PK PRIMARY KEY ( unitguid )
);
temp:='create table temp(
unitguid varchar2(16),
isfz varchar2(2),
passdate date,
orgName varchar2(50)
)';
execute immediate temp;
temp1:='create table temp1
(
unitguid varchar2(100)
,isfz varchar2(2)
,passdate date
,orgName varchar2(50)
)';
CREATE TABLE KgOrgs(
OrgGuid CHAR(36) NOT NULL,
OrgName CLOB NULL,
OrgCode CLOB NULL,
"Level" int NOT NULL,
ParentOrgCode CLOB NULL,
ParentOrgLevelCode CLOB NULL,
NumberOfPeople NUMBER(19,0) NULL,
CONSTRAINT KgOrgs PRIMARY KEY ( OrgGuid )
);
tempOrgs 表就是KgOrgs
phillip629 2019-07-10
- 打赏
- 举报
case when
then 后面数据记过cast后类型已经一致，由于cast数据在字符串中，不知是否已经自动转化为字符串，最终导致数据不一致
ZJHZ_叶 2019-07-10
- 打赏
- 举报
你把语句输出出来然后执行select语句，看看问题在哪就可以了
卖水果的net 2019-07-10
- 打赏
- 举报
把相关的表结构贴上来，要 create table 形式的，方便大家帮你调试。
ZJHZ_叶 2019-07-10
- 打赏
- 举报
case when
then 后面的类型不一致
then 后面的类型不一致
ZJHZ_叶 2019-07-10
- 打赏
- 举报
执行后报错，光标会停在报错的附近位置，你就知道那里可能有错了
ZJHZ_叶 2019-07-10
- 打赏
- 举报
strsql:='create table result as SELECT a.OrgCode,a.OrgName, a.NumberOfPeople, a.MedNum, a.CurrOccupationRatio
, CASE
WHEN (a.CurrOccupationRatio - a.LastOccupationRatio) < 0 THEN ''-''|| CAST((a.LastOccupationRatio - a.CurrOccupationRatio) AS varchar2(100))
WHEN (a.CurrOccupationRatio - a.LastOccupationRatio) = 0 THEN ''0.00''
ELSE ''+''||CAST((a.CurrOccupationRatio - a.LastOccupationRatio) AS varchar2(100))
END AS YearIncrease
, CASE
WHEN (a.CurrOccupationRatio - a.qgOccupationRatio) < 0 THEN ''-''|| CAST((a.qgOccupationRatio - a.CurrOccupationRatio) AS varchar2(100))
WHEN (a.CurrOccupationRatio - a.qgOccupationRatio) = 0 THEN ''0.00''
ELSE ''+''|| CAST((a.CurrOccupationRatio - a.qgOccupationRatio) AS varchar2(100))
END AS QgIncrease
FROM (
SELECT a.OrgCode,a.OrgName, a.NumberOfPeople
, (
SELECT COUNT(1)
FROM temp b
WHERE to_char(a.orgName)=b.orgName
) AS MedNum
, CASE
WHEN nvl(a.numberofpeople,CAST(0 AS number)) = 0 THEN CAST(0 AS number)
ELSE CAST((
SELECT COUNT(1)
FROM temp b
WHERE to_char(a.orgName)=b.orgName
) / (CAST(a.numberofpeople AS decimal) / 1000) * 100 AS decimal(18, 2))
END AS CurrOccupationRatio
, CASE
WHEN nvl(a.numberofpeople, CAST(0 AS number)) = 0 THEN CAST(0 AS number)
ELSE CAST((
SELECT COUNT(1)
FROM temp1 b
WHERE to_char(a.orgName) =b.orgName
) / (CAST(a.numberofpeople AS decimal) / 1000) * 100 AS decimal(18, 2))
END AS LastOccupationRatio, CAST((
SELECT COUNT(1)
FROM jg_info b
WHERE isfz = 1
AND to_char(b.passdate,''yyyy'')='||yearr||'
) / (CAST((
SELECT SUM(numberofpeople)
FROM KgOrgs where LV=1
) AS decimal) / 1000) * 100 AS decimal(18, 2)) AS qgOccupationRatio
FROM tempOrgs a
) a ';
, CASE
WHEN (a.CurrOccupationRatio - a.LastOccupationRatio) < 0 THEN ''-''|| CAST((a.LastOccupationRatio - a.CurrOccupationRatio) AS varchar2(100))
WHEN (a.CurrOccupationRatio - a.LastOccupationRatio) = 0 THEN ''0.00''
ELSE ''+''||CAST((a.CurrOccupationRatio - a.LastOccupationRatio) AS varchar2(100))
END AS YearIncrease
, CASE
WHEN (a.CurrOccupationRatio - a.qgOccupationRatio) < 0 THEN ''-''|| CAST((a.qgOccupationRatio - a.CurrOccupationRatio) AS varchar2(100))
WHEN (a.CurrOccupationRatio - a.qgOccupationRatio) = 0 THEN ''0.00''
ELSE ''+''|| CAST((a.CurrOccupationRatio - a.qgOccupationRatio) AS varchar2(100))
END AS QgIncrease
FROM (
SELECT a.OrgCode,a.OrgName, a.NumberOfPeople
, (
SELECT COUNT(1)
FROM temp b
WHERE to_char(a.orgName)=b.orgName
) AS MedNum
, CASE
WHEN nvl(a.numberofpeople,CAST(0 AS number)) = 0 THEN CAST(0 AS number)
ELSE CAST((
SELECT COUNT(1)
FROM temp b
WHERE to_char(a.orgName)=b.orgName
) / (CAST(a.numberofpeople AS decimal) / 1000) * 100 AS decimal(18, 2))
END AS CurrOccupationRatio
, CASE
WHEN nvl(a.numberofpeople, CAST(0 AS number)) = 0 THEN CAST(0 AS number)
ELSE CAST((
SELECT COUNT(1)
FROM temp1 b
WHERE to_char(a.orgName) =b.orgName
) / (CAST(a.numberofpeople AS decimal) / 1000) * 100 AS decimal(18, 2))
END AS LastOccupationRatio, CAST((
SELECT COUNT(1)
FROM jg_info b
WHERE isfz = 1
AND to_char(b.passdate,''yyyy'')='||yearr||'
) / (CAST((
SELECT SUM(numberofpeople)
FROM KgOrgs where LV=1
) AS decimal) / 1000) * 100 AS decimal(18, 2)) AS qgOccupationRatio
FROM tempOrgs a
) a ';
phillip629 2019-07-10
- 打赏
- 举报
不加'||to_char('-')||' 也报ORA-00905: 缺失关键字
phillip629 2019-07-10
- 打赏
- 举报
create table resultt as SELECT a.OrgCode,a.OrgName, a.NumberOfPeople, a.MedNum, a.CurrOccupationRatio
, CASE
WHEN a.CurrOccupationRatio - a.LastOccupationRatio < 0 THEN '||to_char('-')||'CAST((a.LastOccupationRatio - a.CurrOccupationRatio) AS varchar2(100))
WHEN a.CurrOccupationRatio - a.LastOccupationRatio = 0 THEN 0.00
ELSE '||'+'||' CAST((a.CurrOccupationRatio - a.LastOccupationRatio) AS varchar2(100))AS varchar2(100)
END AS YearIncrease
, CASE
WHEN a.CurrOccupationRatio - a.qgOccupationRatio < 0 THEN '||to_char('-')||'CAST(a.qgOccupationRatio - a.CurrOccupationRatio AS varchar2(100))
WHEN a.CurrOccupationRatio - a.qgOccupationRatio = 0 THEN 0.00
ELSE '||'+'||' CAST(a.CurrOccupationRatio - a.qgOccupationRatio AS varchar2(100)) AS varchar2(100)
END AS QgIncrease
这部分要字符，数据类型不匹配问题已经解决了，现在报ORA-00905: 缺失关键字
ZJHZ_叶 2019-07-10
- 打赏
- 举报
你case when 那里是想要字符（例如显示 +1 ）还是 数字？
phillip629 2019-07-10
- 打赏
- 举报
执行了，报ora00900，辛苦大家了，我先结贴了
ZJHZ_叶 2019-07-10
- 打赏
- 举报
你把execute immediate strsql;
改成 dbms_output.put_line(strsql);
拿到sql执行下就知道问题了
改成 dbms_output.put_line(strsql);
拿到sql执行下就知道问题了
phillip629 2019-07-10
- 打赏
- 举报
谢谢大家，上个问题解决了，还是这段代码又出现了新的问题，ORA-00905: 缺失关键字\nORA-06512:
大佬们帮忙看下啊
strsql:='create table result as SELECT a.OrgCode,a.OrgName, a.NumberOfPeople, a.MedNum, a.CurrOccupationRatio
, CASE
WHEN (a.CurrOccupationRatio - a.LastOccupationRatio) < 0 THEN '||to_char('-')||' CAST((a.LastOccupationRatio - a.CurrOccupationRatio) AS varchar2(100))
WHEN (a.CurrOccupationRatio - a.LastOccupationRatio) = 0 THEN 0.00
ELSE '||'+'||' CAST((a.CurrOccupationRatio - a.LastOccupationRatio) AS varchar2(100))AS varchar2(100)
END AS YearIncrease
, CASE
WHEN (a.CurrOccupationRatio - a.qgOccupationRatio) < 0 THEN '||to_char('-')||' CAST((a.qgOccupationRatio - a.CurrOccupationRatio) AS varchar2(100))
WHEN (a.CurrOccupationRatio - a.qgOccupationRatio) = 0 THEN 0.00
ELSE '||'+'||' CAST((a.CurrOccupationRatio - a.qgOccupationRatio) AS varchar2(100)) AS varchar2(100)
END AS QgIncrease
FROM (
SELECT a.OrgCode,a.OrgName, a.NumberOfPeople
, (
SELECT COUNT(1)
FROM temp b
WHERE to_char(a.orgName)=b.orgName
) AS MedNum
, CASE
WHEN nvl(a.numberofpeople,CAST(0 AS number)) = 0 THEN CAST(0 AS number)
ELSE CAST((
SELECT COUNT(1)
FROM temp b
WHERE to_char(a.orgName)=b.orgName
) / (CAST(a.numberofpeople AS decimal) / 1000) * 100 AS decimal(18, 2))
END AS CurrOccupationRatio
, CASE
WHEN nvl(a.numberofpeople, CAST(0 AS number)) = 0 THEN CAST(0 AS number)
ELSE CAST((
SELECT COUNT(1)
FROM temp1 b
WHERE to_char(a.orgName) =b.orgName
) / (CAST(a.numberofpeople AS decimal) / 1000) * 100 AS decimal(18, 2))
END AS LastOccupationRatio, CAST((
SELECT COUNT(1)
FROM jg_info b
WHERE isfz = 1
AND to_char(b.passdate,''yyyy'')='||yearr||'
) / (CAST((
SELECT SUM(numberofpeople)
FROM KgOrgs where LV=1
) AS decimal) / 1000) * 100 AS decimal(18, 2)) AS qgOccupationRatio
FROM tempOrgs a
) a ';
execute immediate strsql;
ZJHZ_叶 2019-07-10
- 打赏
- 举报
试了一下
- CAST(a.CurrOccupationRatio - a.qgOccupationRatio AS varchar(100)) --number
+CAST(a.CurrOccupationRatio - a.qgOccupationRatio AS varchar(100)) --char
等于
CAST(a.CurrOccupationRatio - a.qgOccupationRatio AS varchar(100))--char
加号并没有用，如果你要加号的话，''+'' 要两个单引号，且上面的类型全要改成字符型
- CAST(a.CurrOccupationRatio - a.qgOccupationRatio AS varchar(100)) --number
+CAST(a.CurrOccupationRatio - a.qgOccupationRatio AS varchar(100)) --char
等于
CAST(a.CurrOccupationRatio - a.qgOccupationRatio AS varchar(100))--char
加号并没有用，如果你要加号的话，''+'' 要两个单引号，且上面的类型全要改成字符型
phillip629 2019-07-10
- 打赏
- 举报
那怎么改啊，+是需要添加的字符
ZJHZ_叶 2019-07-10
- 打赏
- 举报
+ 那一块有问题
加载更多回复（2）
