Oracle报错ORA-01722: 无效数字排查解决方案

在做报表系统的过程中,遇到了一个很有意思的问题,就是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进行单独处理!!

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 203,271评论 5 476
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 85,275评论 2 380
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 150,151评论 0 336
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,550评论 1 273
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,553评论 5 365
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,559评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,924评论 3 395
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,580评论 0 257
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,826评论 1 297
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,578评论 2 320
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,661评论 1 329
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,363评论 4 318
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,940评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,926评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,156评论 1 259
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 42,872评论 2 349
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,391评论 2 342

推荐阅读更多精彩内容