一个很奇怪的问题。
select b.xz_gysz from dc_ehr_all_info a
inner join dc_ehr_tj_all_info b on b.del_flag='1' and b.tj_flag='1' and a.empi_id=b.empi_id
where a.del_flag='1' and a.ehr_state='1' and a.ehr_sex='2'
and a.ehr_csrq ='19581010'
and exists (
select '*' from dc_ehr_tj_all_info m
where m.tj_id = b.tj_id and regexp_like(m.xz_gysz,'^([1-9]\d*|0)(\.\d{1,4})?$')
) and to_number(b.xz_gysz)>=1.81 执行时报ORA-01722:无效数字。
可是我把and to_number(b.xz_gysz)>=1.81去掉且改成
select to_number(b.xz_gysz) from dc_ehr_all_info a
inner join dc_ehr_tj_all_info b on b.del_flag='1' and b.tj_flag='1' and a.empi_id=b.empi_id
where a.del_flag='1' and a.ehr_state='1' and a.ehr_sex='2'
and a.ehr_csrq ='19581010'
and exists (
select '*' from dc_ehr_tj_all_info m
where m.tj_id = b.tj_id and regexp_like(m.xz_gysz,'^([1-9]\d*|0)(\.\d{1,4})?$')
)确能执行成功。
另外我改成如下SQL,也可以执行成功。
select b.xz_gysz from dc_ehr_all_info a
inner join dc_ehr_tj_all_info b on b.del_flag='1' and b.tj_flag='1' and a.empi_id=b.empi_id
where a.del_flag='1' and a.ehr_state='1' and a.ehr_sex='2'
and a.ehr_csrq ='19581010'
and exists (
select '*' from dc_ehr_tj_all_info m
where m.tj_id = b.tj_id and regexp_like(m.xz_gysz,'^([1-9]\d*|0)(\.\d{1,4})?$') order by to_number(b.xz_gysz) desc;
到底是数据问题?还是ORACLE问题?求解
select b.xz_gysz from dc_ehr_all_info a
inner join dc_ehr_tj_all_info b on b.del_flag='1' and b.tj_flag='1' and a.empi_id=b.empi_id
where a.del_flag='1' and a.ehr_state='1' and a.ehr_sex='2'
and a.ehr_csrq ='19581010'
and exists (
select '*' from dc_ehr_tj_all_info m
where m.tj_id = b.tj_id and regexp_like(m.xz_gysz,'^([1-9]\d*|0)(\.\d{1,4})?$')
) and to_number(b.xz_gysz)>=1.81 执行时报ORA-01722:无效数字。
可是我把and to_number(b.xz_gysz)>=1.81去掉且改成
select to_number(b.xz_gysz) from dc_ehr_all_info a
inner join dc_ehr_tj_all_info b on b.del_flag='1' and b.tj_flag='1' and a.empi_id=b.empi_id
where a.del_flag='1' and a.ehr_state='1' and a.ehr_sex='2'
and a.ehr_csrq ='19581010'
and exists (
select '*' from dc_ehr_tj_all_info m
where m.tj_id = b.tj_id and regexp_like(m.xz_gysz,'^([1-9]\d*|0)(\.\d{1,4})?$')
)确能执行成功。
另外我改成如下SQL,也可以执行成功。
select b.xz_gysz from dc_ehr_all_info a
inner join dc_ehr_tj_all_info b on b.del_flag='1' and b.tj_flag='1' and a.empi_id=b.empi_id
where a.del_flag='1' and a.ehr_state='1' and a.ehr_sex='2'
and a.ehr_csrq ='19581010'
and exists (
select '*' from dc_ehr_tj_all_info m
where m.tj_id = b.tj_id and regexp_like(m.xz_gysz,'^([1-9]\d*|0)(\.\d{1,4})?$') order by to_number(b.xz_gysz) desc;
到底是数据问题?还是ORACLE问题?求解
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货