insert into fisheruser.mesage(msgtime,fromnumber,tonumber,fromposition,toposition,msg) (select tablea ttime, tablea.fromnumber, tablea.numberx, nvl((select c.position from fisheruser.msgpos c where to_number(substr(tablea.fromnumber(0,7)) =c.telenumber),'未知'), nvl((select c.position from fisheruser.msgpos c where to_number(substr(tablea.numberx(0,7)) =c.telenumber),'未知'), tablea.msg from ( (select to_date(min(msgtime),'yyyy-mm-dd hh24:mi:ss') as ttime, fromnumber, replace(wm_concat(tonumber),',',',') as numberx, msg from fisheruser.temptable016 group by fromnumber,msg) tablea)) 红色的地方报错
ORA-06502 PL/SQL: numeric or value error string Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints. 上面是oracle的文档,说是要改正你的错误的方法是改数据,所以你的问题趋近于无解,除非数据满足条件<=4000
我的数据库是oracle 10 谢谢。
(select tablea ttime, tablea.fromnumber, tablea.numberx,
nvl((select c.position from fisheruser.msgpos c where to_number(substr(tablea.fromnumber(0,7)) =c.telenumber),'未知'),
nvl((select c.position from fisheruser.msgpos c where to_number(substr(tablea.numberx(0,7)) =c.telenumber),'未知'), tablea.msg
from
(
(select to_date(min(msgtime),'yyyy-mm-dd hh24:mi:ss') as ttime, fromnumber,
replace(wm_concat(tonumber),',',',') as numberx, msg from fisheruser.temptable016
group by fromnumber,msg) tablea))
红色的地方报错
这个出来的结果很长么?
上面是oracle的文档,说是要改正你的错误的方法是改数据,所以你的问题趋近于无解,除非数据满足条件<=4000
我在前面加了一个判断
select * from fisheruser.temptable016 group by fromnumber,msg having count(tonumber>100)。
的单独处理之后,就没有问题了。再次谢谢各位。