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

2013-10-25

oracle ORA-01427:单行子查询返回多个行select * from (select a.*,rownum row_num from (select a.direct

oracle ORA-01427:单行子查询返回多个行
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
[解决办法]
把=改为in试试
[解决办法]

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 = 的那几个地方，看看是不是子查询返回多条记录了。
[解决办法]

引用:

把=改成了in是返回多个值，但是还是报一样的错误

其实解决你的问题很简单，你把你的子查询一个一个全注释掉，然后一个一个放开查询，就知道哪个子查询有问题了。

btw：没有数据环境，真不好说你哪个查询有问题，最可能就是你id= 那个地方。
[解决办法]
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

热点排行