语句
select s.* from t_lubr_standard s,t_data d,t_category c
where s.Period_Change_Lubr is not null
and s.data_id=d.data_id
and d.category=c.category_id
and c.category_id in (1,2,3,5,6)
and s.module_period_change_lubr='8'
and to_char(add_months(s.start_date,s.period_change_lubr),'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')
and s.del_flag=1 and s.approval=2;
报错: ORA-01722: 无效数字发现原因是字段Period_Change_Lubr含有非数字字符,于是过滤掉非数字字符,
将
add_months(s.start_date,s.period_change_lubr),'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')
改为
to_char(add_months(s.start_date, (select distinct to_number(Period_Change_Lubr)
from t_lubr_standard
where REGEXP_LIKE(trim(Period_Change_Lubr),
'^([0-9]+/.[0-9]+)$|^[0-9]+$'))), 'yyyy-mm-dd') =
to_char(sysdate, 'yyyy-mm-dd')又报错:ORA-01427: 单行子查询返回多个行。
哪位大神能给解决一下?
select s.* from t_lubr_standard s,t_data d,t_category c
where s.Period_Change_Lubr is not null
and s.data_id=d.data_id
and d.category=c.category_id
and c.category_id in (1,2,3,5,6)
and s.module_period_change_lubr='8'
and to_char(add_months(s.start_date,s.period_change_lubr),'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')
and s.del_flag=1 and s.approval=2;
报错: ORA-01722: 无效数字发现原因是字段Period_Change_Lubr含有非数字字符,于是过滤掉非数字字符,
将
add_months(s.start_date,s.period_change_lubr),'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')
改为
to_char(add_months(s.start_date, (select distinct to_number(Period_Change_Lubr)
from t_lubr_standard
where REGEXP_LIKE(trim(Period_Change_Lubr),
'^([0-9]+/.[0-9]+)$|^[0-9]+$'))), 'yyyy-mm-dd') =
to_char(sysdate, 'yyyy-mm-dd')又报错:ORA-01427: 单行子查询返回多个行。
哪位大神能给解决一下?
to_char(add_months(s.start_date,之后的这个子查询返回多行呢,试着在子查询+ rownum=1,再看看还报错不,然后再根据你的逻辑下条件确保子查询只返回一行。
from t_lubr_standard
where REGEXP_LIKE(trim(Period_Change_Lubr),
'^([0-9]+/.[0-9]+)$|^[0-9]+$'))
你这个查询就是多行的吧,不用子查询
直接在后面加上过滤
select s.* from t_lubr_standard s,t_data d,t_category c
where s.Period_Change_Lubr is not null
and s.data_id=d.data_id
and d.category=c.category_id
and c.category_id in (1,2,3,5,6)
and s.module_period_change_lubr='8'
and to_char(add_months(s.start_date,s.period_change_lubr),'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')
and s.del_flag=1 and s.approval=2
and REGEXP_LIKE(trim(s.Period_Change_Lubr),
'^([0-9]+/.[0-9]+)$|^[0-9]+$'));