运行如下所示SQL报错提示:ORA-01722: invalid number
SELECT
T1.code
,T1.ifdel
,T1.memo
,T1.createby
,T1.createon
,T1.updateby
,T1.updateon
,T1.companycode
,T1.devicecode
,T1.equipmentcode
,T1.msgtext
FROM Z_MSGRECORD T1
LEFT JOIN Z_EQUIPMENT T6 ON (T1.equipmentcode = T6.code AND T6.IFDEL = 0)
LEFT JOIN Z_MONITOR_DEVICE T0 ON (T0.devicecode = T1.devicecode AND T0.IFDEL = 0)
WHERE T1.ifdel = 0
ORDER BY
T1.createon DESC
查询发现Z_EQUIPMENT表的code是number类型的,但是T1.equipmentcode的类型是varchar2型,类型转换异常报错的,使用to_char()函数,把number转成varchar2型的即可。修改如下这一行即可:
LEFT JOIN Z_EQUIPMENT T6 ON (T1.equipmentcode = TO_CHAR(T6.code) AND T6.IFDEL = 0)
或者使用to_number()函数,把varchar2转成number也可以。
to_number()函数有三个作用:
1.to_number(varchar2 or char,'格式'),例如:
select to_number('000012134') from dual; select to_number('88877') from dual;
2.如果数字在格式范围内的话,就是正确的,否则就是错误的;如:
select to_number('$12345.678', '$999999.99') from dual; select to_number('$12345.678', '$999999.999') from dual;
3.可以用来实现进制转换;16进制转换为10进制:
select to_number('19f','xxx') from dual; select to_number('f','xx') from dual;