Oracle 9i强制转换功能错误 [英] Oracle 9i cast function error

问题描述

当我在下面运行查询时

发生错误。

when i run below query
Error occured.

SELECT 
  cast(substr(T_BILLSUMMARY.OPDNUMBER,1,6) as INTEGER) as OPD,
  substr(T_BILLSUMMARY.OPDNUMBER,8,12) as OPDYEAR,
  T_BILLSUMMARY.BILLTONAME
FROM T_BILLSUMMARY
where cast(substr(T_BILLSUMMARY.OPDNUMBER,1,6) as NUMBER)=10805;

发生错误

Error Occurred

ORA-01722:invalid number

我怎么能解决这个问题...... ???

how can i solve this...???

推荐答案

你好，



试试这个....

Hi,

Try this....

SELECT
CASE WHEN length(translate(substr(T_BILLSUMMARY.OPDNUMBER,1,6),' +-.0123456789',' ')) > 0 
THEN 'Wrong Value'
ELSE cast(substr(T_BILLSUMMARY.OPDNUMBER,1,6) as INTEGER) 
END 
as OPD,
substr(T_BILLSUMMARY.OPDNUMBER,8,12) as OPDYEAR,
T_BILLSUMMARY.BILLTONAME
FROM T_BILLSUMMARY
where cast(substr(T_BILLSUMMARY.OPDNUMBER,1,6) as NUMBER)=10805;

希望这会对你有所帮助。



干杯

Hope this will help you.

Cheers

这篇关于Oracle 9i强制转换功能错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！