ORA-01427 单行子查询返回多于一个行 - 20190130

报错前:

select  
   (select m.contract_subid
           from xs.T_S_CONTRACT_D m
         where m.seg_no = d.seg_no
            and m.contract_subid = d.contract_subid) "dd_contract_subid"
       ,(select m.spec
           from xs.T_S_CONTRACT_D m
         where m.seg_no = d.seg_no
            and m.contract_id = d.contract_id) "dd_spec"
  from xs.T_S_SETTLE_M a, xs.t_s_settle_d d
  where 1=1
    and a.seg_no = d.seg_no
    and a.settle_id = d.settle_id

修改后:

  select  
   (select m.contract_subid
           from xs.T_S_CONTRACT_D m
         where m.seg_no = d.seg_no
            and m.contract_id = d.contract_id
            and m.contract_subid = d.contract_subid) "dd_contract_subid"
       ,(select m.spec
           from xs.T_S_CONTRACT_D m
         where m.seg_no = d.seg_no
            and m.contract_id = d.contract_id
            and m.contract_id = d.contract_id) "dd_spec"
  from xs.T_S_SETTLE_M a, xs.t_s_settle_d d
  where 1=1
    and a.seg_no = d.seg_no
    and a.settle_id = d.settle_id

因为xs.T_S_CONTRACT_D和 xs.t_s_settle_d 都是子表,直接用 '合同号' 关联自然就出现都行信息,所以在加上一个 '合同子项号' 就没问题了

子查询注意你加的关联条件,避免不存在的问题