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);