在做报表系统的过程中,遇到了一个很有意思的问题,就是Oracle报了一个错java.sql.SQLException: ORA-01722: 无效数字
,正常来说,报该错就意味着数据类型不匹配,但是因为同一sql语句,多加了一个条件,就查出结果,不加条件,反而报错,所以就意味着,可能数据有误(或者语句有误),但是哪一条数据有误,肉眼难以识别!
有一个机构表,存10个机构,is_use=‘0’
意味着无效,不统计,is_use=‘1’
意味着有效,参与统计,先看sql:
private final String publicSql = "SELECT r.ynccb,"
+ "ROUND((r.p1_cp+r.p2_cp+r.p3_cp+r.p4_cp+NVL(t.tie_account,0)*10000)+(r.p5_cp+r.p6_cp+r.p7_cp+r.p8_cp+r.p9_cp+r.p10_cp),3) eightBen,"
+ "ROUND((r.p1_year_inc+r.p2_year_inc+r.p3_year_inc+r.p4_year_inc+r.p5_year_inc+r.p6_year_inc+r.p7_year_inc+r.p8_year_inc+r.p9_year_inc+r.p10_year_inc),3) eightYear,"
+ "ROUND((r.p1_month_inc+r.p2_month_inc+r.p3_month_inc+r.p4_month_inc+r.p5_month_inc+r.p6_month_inc+r.p7_month_inc+r.p8_month_inc+r.p9_month_inc+r.p10_month_inc),3) eightMonth,"
+ "ROUND((r.p1_day_inc+r.p2_day_inc+r.p3_day_inc+r.p4_day_inc+r.p5_day_inc+r.p6_day_inc+r.p7_day_inc+r.p8_day_inc+r.p9_day_inc+r.p10_day_inc),3) eightDay,"
+ "ROUND(DECODE(ba.plan_8add1,0,9999,(r.p1_year_inc+r.p2_year_inc+r.p3_year_inc+r.p4_year_inc+r.p5_year_inc+r.p6_year_inc+r.p7_year_inc+r.p8_year_inc+r.p9_year_inc+r.p10_year_inc)/ba.plan_8add1*100),3) eightPlan, "
+ "ROUND(DECODE(ba.org_num,0,9999,(r.p1_year_inc+r.p2_year_inc+r.p3_year_inc+r.p4_year_inc+r.p5_year_inc+r.p6_year_inc+r.p7_year_inc+r.p8_year_inc+r.p9_year_inc+r.p10_year_inc)/ba.org_num),3) eightAdd,"
+ "ROUND((r.p1_cp+r.p2_cp+r.p3_cp+r.p4_cp+NVL(t.tie_account,0)*10000),3) twoBen,"
+ "ROUND((r.p1_year_inc+r.p2_year_inc+r.p3_year_inc+r.p4_year_inc),3) twoYear,"
+ "ROUND((r.p1_month_inc+r.p2_month_inc+r.p3_month_inc+r.p4_month_inc),3) twoMonth,"
+ "ROUND((r.p1_day_inc+r.p2_day_inc+r.p3_day_inc+r.p4_day_inc),3) twoDay,"
+ "ROUND(DECODE(ba.plan_2add1,0,9999,((r.p1_year_inc+r.p2_year_inc+r.p3_year_inc+r.p4_year_inc)/ba.plan_2add1*100)),3) twoPlan, "
+ "ROUND((r.p5_cp+r.p6_cp+r.p7_cp+r.p8_cp+r.p9_cp+r.p10_cp),3) perBen,"
+ "ROUND((r.p5_year_inc+r.p6_year_inc+r.p7_year_inc+r.p8_year_inc+r.p9_year_inc+r.p10_year_inc),3) perYear,"
+ "ROUND((r.p5_month_inc+r.p6_month_inc+r.p7_month_inc+r.p8_month_inc+r.p9_month_inc+r.p10_month_inc),3) perMonth,"
+ "ROUND((r.p5_day_inc+r.p6_day_inc+r.p7_day_inc+r.p8_day_inc+r.p9_day_inc+r.p10_day_inc),3) perDay, "
+ "ROUND(DECODE(ba.personal_plan,0,9999,(r.p5_year_inc+r.p6_year_inc+r.p7_year_inc+r.p8_year_inc+r.p9_year_inc+r.p10_year_inc)/ba.personal_plan*100),3) perPlan "
+ " FROM ccb_rpt_r207 r,ccb_rpt_tie t,ccb_rpt_base ba "
+ " WHERE r.organ=t.ynccb_second_no AND r.organ=ba.org_no AND "
+ " r.intr_date=t.intr_date AND r.intr_date='";
这个是公共的sql部分,往后添加条件,即可查相应结果:
String sumHql = null ;
// 云南
if(datasource.toUpperCase().equals("PV")){
sumHql = publicSql + intr_date + "' and r.organ IN (" + ynCheckSql +")";
}
// 昆明
if(datasource.toUpperCase().equals("KM")){
sumHql = publicSql + intr_date + "' and r.organ in (" + kmSql + ")";
}
查昆明能查出结果,但是查云南的时候,就报错,所以一怒之下,把ynCheckSql语句结果查出来,一条一条检验,还真发现了问题:
针对德宏这一个地方,查询的时候报了ORA-01722: 无效数字
错误,仔细查看数据源,发现数据也没什么问题,因为计算过程中,会报除数不能为0的错,而该条数据有一个值(该值是分母,也就是除数
),我们都知道,除数不能为0,所以Oracle针对除数为0的情况,有一个函数:
decode函数,具体用法如下:
select decode(b,0,'-',a/b) from table;
当b = 0时,返回-,否则返回a/b的结果。
所以为了直接返回结果,除数为0时直接返回-
如这个sql语句:ROUND(DECODE(ba.personal_plan,0,'-',(r.p5_year_inc+r.p6_year_inc+r.p7_year_inc+r.p8_year_inc+r.p9_year_inc+r.p10_year_inc)/ba.personal_plan*100),3) perPlan
我们都知道ROUND函数是保留小数位数,如果返回-
,ROUND函数肯定会包无效数字错误,所以如果除数为0,直接返回特殊字符9999即可,后面再对9999进行单独处理!!