用友T6软件将NUMERIC转换为数据类型VARCHAR时发生算术溢出错误。将NUMERIC转换为数据类型VARCHAR时发生算术溢出错误。
将NUMERIC转换为数据类型VARCHAR时发生算术溢出错误。原因分析:问题解答: 导致这个问题的原因为bs_grm_cstotal该数据库表中时间字段格式异常导致的,佳成公司已开发出脚本解决。 SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOALTER PROCEDURE [bs_grm_cstotal] (@begindate datetime ='2007-09-18',@corp varchar(32)='1001')as create table #transubjecttotal_temp1( pk_corp char(4) null, tt_time char(10) null, pk_cs varchar(40) null, fee_mny numeric(20,8) null, pay_mny numeric(20,8) null, per_day varchar(20) null, month_mny numeric(20,8) null, per_month varchar(20) null, year_mny numeric(20,8) null, per_year varchar(20) null, cscsign int null ) insert into #transubjecttotal_temp1(pk_corp, tt_time, pk_cs, fee_mny, pay_mny, month_mny, year_mny, cscsign) select a.pk_corp, a.tt_time, a.pk_cs, tt_day, 0, tt_month, tt_year, 0 from grm_transubjecttotal a inner join grm_cashsubject b on a.pk_corp=b.pk_corp and a.pk_cs=b.pk_cs where a.pk_corp=@corp and a.tt_time=@begindate and b.cscsign=0 insert into #transubjecttotal_temp1(pk_corp, tt_time, pk_cs, fee_mny, pay_mny, month_mny, year_mny, cscsign) select a.pk_corp, a.tt_time, a.pk_cs, 0, tt_day, tt_month, tt_year, 1 from grm_transubjecttotal a inner join bd_payment b on a.pk_corp=b.pk_corp and a.pk_cs=b.paymentcode where a.pk_corp=@corp and a.tt_time=@begindate update #transubjecttotal_temp1 set per_day= FLOOR (case when isnull(b.tt_day,0) > 0 then ( (isnull(a.fee_mny,0)-isnull(b.tt_day,0))/isnull(b.tt_day,0) )*100 when isnull(b.tt_day,0) = 0 then null when isnull(b.tt_day,0) < 0 then (isnull(a.fee_mny,0)-isnull(b.tt_day,0))*100 end), per_month= FLOOR (case when isnull(b.tt_month,0) > 0 then ( (isnull(a.month_mny,0)-isnull(b.tt_month,0))/isnull(b.tt_month,0) )*100 when isnull(b.tt_month,0) = 0 then null when isnull(b.tt_month,0) < 0 then (isnull(a.month_mny,0)-isnull(b.tt_month,0))*100 end), per_year= FLOOR (case when isnull(b.tt_year,0) > 0 then ( (isnull(a.year_mny,0)-isnull(b.tt_year,0))/isnull(b.tt_year,0) )*100 when isnull(b.tt_year,0) = 0 then null when isnull(b.tt_year,0) < 0 then (isnull(a.year_mny,0)-isnull(b.tt_year,0))*100 end) from #transubjecttotal_temp1 a left join (select pk_corp, pk_cs, tt_day, tt_month, tt_year from grm_transubjecttotal where pk_corp=@corp and tt_time=convert(char(10), dateadd(year, -1, cast(@begindate as datetime)), 120) ) b on a.pk_corp=b.pk_corp and a.pk_cs=b.pk_cs where a.cscsign=0 update #transubjecttotal_temp1 set per_day= FLOOR (case when isnull(b.tt_day,0) > 0 then ( (isnull(a.pay_mny,0)-isnull(b.tt_day,0))/isnull(b.tt_day,0) )*100 when isnull(b.tt_day,0) = 0 then null when isnull(b.tt_day,0) < 0 then (isnull(a.pay_mny,0)-isnull(b.tt_day,0))*100 end), per_month= FLOOR (case when isnull(b.tt_month,0) > 0 then ( (isnull(a.month_mny,0)-isnull(b.tt_month,0))/isnull(b.tt_month,0) )*100 when isnull(b.tt_month,0) = 0 then null when isnull(b.tt_month,0) < 0 then (isnull(a.month_mny,0)-isnull(b.tt_month,0))*100 end), per_year= FLOOR (case when isnull(b.tt_year,0) > 0 then ( (isnull(a.year_mny,0)-isnull(b.tt_year,0))/isnull(b.tt_year,0) )*100 when isnull(b.tt_year,0) = 0 then null when isnull(b.tt_year,0) < 0 then (isnull(a.year_mny,0)-isnull(b.tt_year,0))*100 end) from #transubjecttotal_temp1 a left join (select pk_corp, pk_cs, tt_day, tt_month, tt_year from grm_transubjecttotal where pk_corp=@corp and tt_time=convert(char(10), dateadd(year, -1, cast(@begindate as datetime)), 120) ) b on a.pk_corp=b.pk_corp and a.pk_cs=b.pk_cs where a.cscsign=1 select a.pk_corp as '公司编码', a.tt_time as '营业日期', a.pk_cs as '科目主键', b.cscode as '科目编码', b.csname as '科目名称', isnull(b.cscode, '')+' '+isnull(b.csname, '') as '科目', case when isnull(a.fee_mny,0)=0 then null else a.fee_mny end as '消费金额', case when isnull(a.pay_mny,0)=0 then null else a.pay_mny end as '结算金额', a.per_day as '去年同日(%)', a.month_mny as '本月累计', a.per_month as '去年同月(%)', a.year_mny as '本年累计', a.per_year as '去年同年(%)', a.cscsign as '标志' from #transubjecttotal_temp1 a left join grv_grm_printsubject b on a.pk_corp=b.pk_corp and a.pk_cs=b.pk_cs where isnull(a.fee_mny, 0) <> 0 or isnull(a.pay_mny, 0) <> 0 or isnull(a.month_mny, 0) <> 0 or isnull(a.year_mny, 0) <> 0 union all select @corp , @begindate, '', '', '', '[合计]', isnull(sum(a.fee_mny), 0), isnull(sum(a.pay_mny), 0), null, null, null, null, null, 2 from #transubjecttotal_temp1 a where isnull(a.fee_mny, 0) <> 0 or isnull(a.pay_mny, 0) <> 0 or isnull(a.month_mny, 0) <> 0 or isnull(a.year_mny, 0) <> 0 order by 标志, 科目编码 GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO