select * from journalyear a
where a.cccjid='10001870800'
and a.systemlibraryid= '002016002'
and (regexp_like(a.cvolume,'^[0-9]*$') or a.cvolume is null)
and (regexp_like(a.cissue,'^[0-9]*$') or a.cissue is null)
and a.cyear=(
select ''||min(to_number(b.cyear))||''
from journalyear b
where b.cccjid='10001870800'
and b.systemlibraryid='002016002'
and regexp_like(b.cyear,'^[0-9]+$')
and to_number(b.cyear)<2009)
[/code]
这句中我已经用 regexp_like(b.cyear,'^[0-9]+$')限定住了cyear,肯定是数字,但为什么还报 ORA-01722: invalid number错误,(如果将to_number(b.cyear)<2009或改为to_number(b.cyear)<'2009'就不报错了)
where a.cccjid='10001870800'
and a.systemlibraryid= '002016002'
and (regexp_like(a.cvolume,'^[0-9]*$') or a.cvolume is null)
and (regexp_like(a.cissue,'^[0-9]*$') or a.cissue is null)
and a.cyear=(
select ''||min(to_number(b.cyear))||''
from journalyear b
where b.cccjid='10001870800'
and b.systemlibraryid='002016002'
and regexp_like(b.cyear,'^[0-9]+$')
and to_number(b.cyear)<2009)
[/code]
这句中我已经用 regexp_like(b.cyear,'^[0-9]+$')限定住了cyear,肯定是数字,但为什么还报 ORA-01722: invalid number错误,(如果将to_number(b.cyear)<2009或改为to_number(b.cyear)<'2009'就不报错了)
(如果将to_number(b.cyear) <2009或改为to_number(b.cyear) <'2009'就不报错了)
改为:
(如果将to_number(b.cyear) <2009删掉或改为to_number(b.cyear) <'2009'就不报错了)
2009
建议考虑一下num的范围,和上面cyear的转换结果的数值