oracle ORA-01427:单行子查询返回多个行

Question

select * from (select a.*,rownum row_num from (select a.directflag,(select sheettypename from 
sheettype where id=(select sheettypeid from moduletype where moduleid=trim(a.sheetidruleid))) 
sheettypename,(select code||'-'||abbrev from channel where channelid=a.outcomecustomer)
outcustomer,(select code||'-'||abbrev from channel where channelid=a.incomecustomer) 
incustomer,(select balancetype from balancetypeset where id=a.balancetypeid)
 balancetype,(select goodsno||'-'||goodsname from goods where goodsid=a.goodsid) 
 goodsnanme,a.quantity,a.price,a.amount,a.accdate from channelaccount a where 1=1 
 and a.incomecustomer='acd488efdcc8f4a0ae2e42ebbe8e2f72') a) where row_num>0 and row_num<=20执行报ORA-01427:单行子查询返回多个行
求高人指点oracle

Answer 1

select * from (select a.*,rownum row_num from (select a.directflag,
(select sheettypename from sheettype where id in(select sheettypeid from moduletype
 where moduleid=trim(a.sheetidruleid))) sheettypename,(select code||'-'||abbrev from channel 
 where channelid=a.outcomecustomer) outcustomer,(select code||'-'||abbrev from channel 
 where channelid=a.incomecustomer) incustomer,(select balancetype from balancetypeset 
 where id=a.balancetypeid) balancetype,(select goodsno||'-'||goodsname from goods 
 where goodsid=a.goodsid) goodsnanme,a.quantity,a.price,a.amount,a.accdate from
 channelaccount a where 1=1 and a.incomecustomer='acd488efdcc8f4a0ae2e42ebbe8e2f72') a)
  where row_num>0 and row_num<=20改了in还是一样ORA-01427:单行子查询返回多个行

Answer 2

SELECT *
  FROM (SELECT A.*, ROWNUM ROW_NUM
          FROM (SELECT A.DIRECTFLAG,
                       (SELECT SHEETTYPENAME
                          FROM SHEETTYPE
                         WHERE ID =
                               (SELECT SHEETTYPEID
                                  FROM MODULETYPE
                                 WHERE MODULEID = TRIM(A.SHEETIDRULEID))) SHEETTYPENAME,
                       (SELECT CODE || '-' || ABBREV
                          FROM CHANNEL
                         WHERE CHANNELID = A.OUTCOMECUSTOMER) OUTCUSTOMER,
                       (SELECT CODE || '-' || ABBREV
                          FROM CHANNEL
                         WHERE CHANNELID = A.INCOMECUSTOMER) INCUSTOMER,
                       (SELECT BALANCETYPE
                          FROM BALANCETYPESET
                         WHERE ID = A.BALANCETYPEID) BALANCETYPE,
                       (SELECT GOODSNO || '-' || GOODSNAME
                          FROM GOODS
                         WHERE GOODSID = A.GOODSID) GOODSNANME,
                       A.QUANTITY,
                       A.PRICE,
                       A.AMOUNT,
                       A.ACCDATE
                  FROM CHANNELACCOUNT A
                 WHERE 1 = 1
                   AND A.INCOMECUSTOMER = 'acd488efdcc8f4a0ae2e42ebbe8e2f72') A)
 WHERE ROW_NUM > 0
   AND ROW_NUM <= 20
就检查下你 id = 的那几个地方，看看是不是子查询返回多条记录了。

Answer 3

 where id=(select sheettypeid from moduletype where moduleid=trim(a.sheetidruleid)
应该是这里有问题吧。。返回多个值了!

Answer 4

其实解决你的问题很简单，你把你的子查询一个一个全注释掉，然后一个一个放开查询，就知道哪个子查询有问题了。
btw：没有数据环境，真不好说你哪个查询有问题，最可能就是你id= 那个地方。

Answer 5

select *
  from (select a.*, rownum row_num
          from (select a.directflag,
                       (select wm_concat(sheettypename)
                          from sheettype
                         where id in
                               (select sheettypeid
                                  from moduletype
                                 where moduleid = trim(a.sheetidruleid))) sheettypename,
                       (select code || '-' || abbrev
                          from channel
                         where channelid = a.outcomecustomer) outcustomer,
                       (select code || '-' || abbrev
                          from channel
                         where channelid = a.incomecustomer) incustomer,
                       (select balancetype
                          from balancetypeset
                         where id = a.balancetypeid) balancetype,
                       (select goodsno || '-' || goodsname
                          from goods
                         where goodsid = a.goodsid) goodsnanme,
                       a.quantity,
                       a.price,
                       a.amount,
                       a.accdate
                  from channelaccount a
                 where 1 = 1
                   and a.incomecustomer = 'acd488efdcc8f4a0ae2e42ebbe8e2f72') a)
 where row_num > 0
   and row_num <= 20

Answer 6

检查你每一个使用子查询的地方，问题应该是出在select子句中：
SHEETTYPENAME、OUTCUSTOMER、INCUSTOMER、BALANCETYPE、GOODSNANME这些字段都是通过在select子句中进行子查询查出来的，应该是这里存在查询出结果的数量大于1的子句
比如说SELECT CODE || '-' || ABBREV  FROM CHANNEL  WHERE CHANNELID = A.OUTCOMECUSTOMER
通过CHANNELID = A.OUTCOMECUSTOMER查询出的结果必须不能多于1条，如果CHANNELID有重复的就会报单行子查询返回多行