首页 > 代码库 > ORA-01722: invalid number,ORA-12801

ORA-01722: invalid number,ORA-12801

SQL:

SELECT

/*+ parallel(a,32) */ a.id

          ,a.data_date

          ,a.mobile_num

          ,a.mobile_code

          ,b.prov AS mobile_prov

          ,b.city AS mobile_city

          ,b.mobile_oper

          ,a.psam

          ,a.psam_code

          ,c.detail_type

          ,c.busi_type

          ,c.sum_type

          ,a.trancde

          ,d.trans_name

          ,d.lv4_name

          ,d.lv3_name

          ,d.lv2_name

          ,d.lv1_name

          ,d.lv0_name

          ,a.merno

          ,a.outcdno

          ,a.outcdno_bin

          ,e.card_name AS outcdno_name

          ,e.card_type AS outcdno_type

          ,e.card_type_name AS outcdno_type_name

          ,e.bank_name AS out_bank_name

          ,e.bank_short_name AS out_bank_short_name

          ,a.incdno

          ,a.incdno_bin

          ,f.card_name AS incdno_name

          ,f.card_type AS incdno_type

          ,f.card_type_name AS incdno_type_name

          ,f.bank_name AS in_bank_name

          ,f.bank_short_name AS in_bank_short_name

          ,a.s_no

          ,a.terminal_area

          ,a.terminal_id

          ,a.pro_no

          ,a.n_platform

          ,a.org_id

          ,a.pay_state

          ,a.pay_respn

          ,a.trans_respn

          ,a.de37

          ,a.is_sucess

          ,a.total_am

          ,a.pro_am

          ,a.free_am

FROM edw.dw_fact_trans partition(DW_MONTH_201303) a

LEFT JOIN edm.d_mobile_info b

ON a.mobile_code = b.mobile_code

LEFT JOIN edm.d_psam_card_info c

ON a.psam_code = c.psam_ab_head

LEFT JOIN edm.d_trancde_info d

ON a.trancde = d.trans_code

LEFT JOIN edm.d_cardinfo_apply e

ON a.outcdno_bin = e.card_bin

LEFT JOIN edm.d_cardinfo_apply f

ON a.incdno_bin = f.card_bin      ;

报错信息:

SELECT

*

ERROR at line 1:

ORA-12801: error signaled in parallel query server P003, instance db1:db1 (1)

ORA-01722: invalid number

原因分析:

存在将字符和数字进行比较。

解决: a.mobile_code = to_char(b.mobile_code)

ORA-01722: invalid number,ORA-12801