ORA-01427:单行子查询返回多行01427.00000 -“单行子查询返回多行”
ORA-01427:单行子查询返回多行01427.00000 -“单行子查询返回多行”
提问于 2017-06-01 09:37:56
下面是给我带来错误的查询：
- 如果ed_temp和ed_indv_counters使用edg_trace_id连接。
- 如果Ed_eligiblity把current_elig_ind作为'P‘，那么就用edg_trace_id edg_trace_id和current_elig_ind 'A’。
查询：
select distinct
i.edg_trace_id,
i.*
from ed_indv_counters i,
ed_temp e
where e.case_num = '720335'
and e.payment_beg_dt<'30-JUL-2017'
and e.program_cd = 'TF'
and e.delete_sw='N'
and di_ind='Y'
and counter_type_cd='TLP'
and i.edg_trace_id in (
CASE e.current_elig_ind
WHEN 'P' then e.edg_trace_id
ELSE (
Select e1.edg_trace_id
from ed_temp e1
where e1.case_num='720335'
and e1.program_cd = 'TF'
and e1.delete_sw='N'
and e1.current_elig_ind='A'
and e1.payment_beg_dt not in (
select payment_beg_dt
from ed_temp e2
where e2.current_elig_ind='P'
and e2.case_num='720335'
and e2.delete_sw='N'
and e2.program_cd = 'TF'
)
)
END
)
order by i.counter_begin_dt; 回答 1
Stack Overflow用户
发布于 2017-06-01 10:29:18
您可以尝试使用UNION ALL和互斥筛选器，而不是使用CASE语句：
select distinct
i.edg_trace_id,
i.*
from ed_indv_counters i,
ed_temp e
where e.case_num = '720335'
and e.payment_beg_dt < DATE '2017-07-30' -- Use a date literal rather than string
and e.program_cd = 'TF'
and e.delete_sw='N'
and di_ind='Y'
and counter_type_cd='TLP'
and i.edg_trace_id in (
SELECT e.edg_trace_id
FROM DUAL
WHEN e.current_elig_ind = 'P'
UNION ALL
Select e1.edg_trace_id
from ed_temp e1
where ( e.current_elig_ind <> 'P' OR e.current_elig_ind IS NULL )
and e1.case_num='720335'
and e1.program_cd = 'TF'
and e1.delete_sw='N'
and e1.current_elig_ind='A'
and e1.payment_beg_dt not in (
select payment_beg_dt
from ed_temp e2
where e2.current_elig_ind='P'
and e2.case_num='720335'
and e2.delete_sw='N'
and e2.program_cd = 'TF'
)
)
order by i.counter_begin_dt; 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44303913
复制相关文章
相似问题
腾讯云开发者
