今天在解决问题时遇到了这个问题,ORA-01427: 单行子查询返回多个行
后来发现是因为在子查询中SQL语句返回多条结果。
子查询:
select distinct CUS_GOODSNO from v_biz_goods_match d
where d.SUP_GOODSNO = i.GOODSID and d.flag = 0 and d.deleted = 0 and d.CUS_ORGID = i.CUSTOMCODEINSYS and d.SUP_ORGID = 'S00000001'
SQL语句:
select
i.create_date,
i.create_user,
i.id,
i.update_date,
i.update_user,
i.deleted,
(select distinct CUS_GOODSNO from v_biz_goods_match d
where d.SUP_GOODSNO = i.GOODSID and d.flag = 0 and d.deleted = 0 and d.CUS_ORGID = i.CUSTOMCODEINSYS and d.SUP_ORGID = 'S00000001' and rownum=1) cusgoodsno,
from BIZ_HRHN_RETURN_INVOICE i
where i.CUSTOMCODEINSYS = 'H00000012' order by i.create_date desc;
在子查询中加上and rownum = 1,强制返回一条结果。