oracle ORA-01722 报错 "invalid number"

今天写的SQL报错，报错信息是  ORA-01722 "invalid number"

select /*+parallel(l 5)*/
 e.customcode "账户内码",
 e.customphone "手机号码",
 sum(l.betamount) / 100 "投注金额(元)",
 min(l.transdate) "投注时间",
 l.ticketcode "票号"
  from ELMP_REPORT_SALEDETAIL        l,
       ELMP_REPORT_CUSTOMACCOUNTFLOW w,
       ELMP_REPORT_CUSTOMDETAIL      e
 where l.clientflownum = w.transflowid
   and w.customcode = e.customcode
   and TO_CHAR(TRANSDATE, 'YYYYMMDD') = '&1'
   and w.customtransdate >= to_date('&1', 'YYYYMMDD') - 1
   and w.customtransdate < to_date('&1', 'YYYYMMDD') + 2
   and substr(l.ticketcode, -3) = '111'
 group by l.ticketcode, e.customcode, e.customphone
 order by l.ticketcode, min(l.transdate);

仔细检查后，发现原来是关联的字段的数据类型不一致，一个是number类型，一个是varchar2类型

需要转换数据类型，更改后的SQL如下

select /*+parallel(l 5)*/
 e.customcode "账户内码",
 e.customphone "手机号码",
 sum(l.betamount) / 100 "投注金额(元)",
 min(l.transdate) "投注时间",
 l.ticketcode "票号"
  from ELMP_REPORT_SALEDETAIL        l,
       ELMP_REPORT_CUSTOMACCOUNTFLOW w,
       ELMP_REPORT_CUSTOMDETAIL      e
 where  l.clientflownum = to_char(w.transflowid)
   and w.customcode = e.customcode
   and TO_CHAR(TRANSDATE, 'YYYYMMDD') = '&1'
   and w.customtransdate >= to_date('&1', 'YYYYMMDD') - 1
   and w.customtransdate < to_date('&1', 'YYYYMMDD') + 2
   and substr(l.ticketcode, -3) = '111'
 group by l.ticketcode, e.customcode, e.customphone
 order by l.ticketcode, min(l.transdate);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-1819169/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-1819169/