萌新接觸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
uj5u.com熱心網友回復：
你看看是否有資料不是數字，而進行了sum運算操作。比如 有的列中存盤了字符型的，'LKDJF' 進行了運算，就提示無效數字
uj5u.com熱心網友回復：
sum運算操作的列都是number型別，其中的保存的資料都是數字，這個我已經查過了，之前我以為where中的id以及shopid和deptid的型別不相同，然后我查了表，都為number型別的uj5u.com熱心網友回復：
select levelvalue into V_levelvalue from deptlevel這個表的levelvalue 的資料型別呢？
uj5u.com熱心網友回復：
把陳述句塊改成程序debug一下uj5u.com熱心網友回復：
levelvalue 型別是numberuj5u.com熱心網友回復：
rpt_saledept 的表結構也貼一下，要 create table 形式的。uj5u.com熱心網友回復：
-- Create tablecreate 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表的架構
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/9962.html
標籤：非技術區