请各位大佬过来看看这是什么问题引起的报错

[yoco zhang]

萌新接触oracle不多，第二次写临时表，报ora-01722，无效数字，但是找了很久还是没有找到原因，请各位大佬帮帮忙，谢谢
create global temporary table TT_SALEDEPTTEMP
(
shopid VARCHAR2(4),
shopname VARCHAR2(32),
deptid INTEGER,
deptname VARCHAR2(20),
costvalue NUMBER(20,2),
salevalue NUMBER(20,2),
discvalue NUMBER(20,2),
resalevalue NUMBER(20,2),
hbcostvalue NUMBER(20,2),
hbsalevalue NUMBER(20,2),
hbdiscvalue NUMBER(20,2),
hbresalevalue NUMBER(20,2)

)
on commit delete rows;
-- 数据准备
declare V_levelvalue number;
begin select levelvalue into V_levelvalue from deptlevel where deptlevelid=1;
insert into TT_SALEDEPTTEMP
select
a.shopid,b.name shopname,
a.deptid,d.name deptname,
a.costvalue,a.salevalue,a.discvalue,a.resalevalue,
c.hbcostvalue,c.hbsalevalue,c.hbdiscvalue,c.hbresalevalue
from
(select
shopid,
floor(deptid/v_levelvalue) deptid,
sum(costvalue) costvalue,
sum(salevalue) salevalue,
sum(discvalue) discvalue,
sum(salevalue-discvalue) resalevalue
from
rpt_saledept where 1=1 and to_char(sdate,'YYYY-MM-DD') between '2020-06-07' and '2020-06-07'
group by shopid,floor(deptid/v_levelvalue)) a,
shop b,
(select shopid,
floor(deptid/v_levelvalue) deptid,
sum(costvalue) hbcostvalue,
sum(salevalue) hbsalevalue,
sum(discvalue) hbdiscvalue,
sum(salevalue-discvalue) hbresalevalue
from rpt_saledept where 1=1 and to_char(sdate,'YYYY-MM-DD') between '2020-06-07' and '2020-06-07'
group by shopid, floor(deptid/v_levelvalue)) c,
(select id,name from dept union select id,name from sgroup )d
where a.shopid=b.id and a.shopid=c.shopid and a.deptid=c.deptid and a.deptid=d.id;
end;
--Error: General SQL error.
ORA-01722: 无效数字
ORA-06512: 在 line 1

[sxq129601]

把语句块改成过程debug一下

[yoco zhang]

levelvalue 类型是number

[卖水果的net]

rpt_saledept 的表结构也贴一下，要 create table 形式的。

[js14982]

你看看是否有数据不是数字，而进行了sum运算操作。 比如 有的列中存储了字符型的，'LKDJF' 进行了运算，就提示无效数字

[yoco zhang]

sum运算操作的列都是number类型，其中的保存的数据都是数字，这个我已经查过了，之前我以为where中的id以及shopid和deptid的类型不相同，然后我查了表，都为number类型的

[js14982]

select levelvalue into V_levelvalue from deptlevel 这个表的levelvalue 的数据类型呢？

[yoco zhang]

-- Create table create table RPT_SALEDEPT ( sdate DATE not null, shopid VARCHAR2(4) not null, deptid NUMBER(10) not null, saletaxrate NUMBER(20,8) not null, salerate NUMBER(20,8) not null, salevalue NUMBER(20,8) not null, discvalue NUMBER(20,8) not null, saletaxvalue NUMBER(20,8) not null, costvalue NUMBER(20,8) not null, msaletotal NUMBER(20,8) not null, mdisctotal NUMBER(20,8) not null, mtaxtotal NUMBER(20,8) not null, mcostvalue NUMBER(20,8) not null ) tablespace DATA_SPC pctfree 10 initrans 1 maxtrans 255 storage ( initial 64 next 1 minextents 1 maxextents unlimited ); -- Add comments to the columns comment on column RPT_SALEDEPT.sdate is '日期(date)'; comment on column RPT_SALEDEPT.shopid is '店号'; comment on column RPT_SALEDEPT.deptid is '小类号'; comment on column RPT_SALEDEPT.saletaxrate is '销项税率'; comment on column RPT_SALEDEPT.salerate is '本日销售占比'; comment on column RPT_SALEDEPT.salevalue is '销售额'; comment on column RPT_SALEDEPT.discvalue is '折扣额'; comment on column RPT_SALEDEPT.saletaxvalue is '销项税金'; comment on column RPT_SALEDEPT.costvalue is '销售成本'; comment on column RPT_SALEDEPT.msaletotal is '月销售额累计'; comment on column RPT_SALEDEPT.mdisctotal is '月折扣额累计'; comment on column RPT_SALEDEPT.mtaxtotal is '月销项税金累计'; comment on column RPT_SALEDEPT.mcostvalue is '月销售成本累计'; -- Create/Recreate primary, unique and foreign key constraints alter table RPT_SALEDEPT add constraint PK_RPT_SALEDEPT primary key (SDATE, SHOPID, DEPTID, SALETAXRATE) using index tablespace INDX_SPC pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); 这是rpt_saledept表的架构