ORA-01427: single-row subquery returns more than one row select statement

Question

I have two Result sets . I intend to get the records which are in the first result set and not in the second: My first result set:

select * from (select k.foracid,k.acct_name, k.sol_id BRANCH_CODE, s.SOL_DESC BRANCH_NAME,k.ACCT_OPN_DATE,k.schm_code 
from tbaadm.gam k, tbaadm.sol s
where acct_cls_flg = 'N' and acct_ownership<>'O'
and s.sol_id = k.sol_id
and k.schm_type in('CAA', 'SBA', 'ODA', 'TDA'));

Here Foracid is unique:

My second resultSet:

select acctid from (select r.signid,
(case when r.acctid is not null then r.acctid else (select g.foracid from tbaadm.gam g where g.cif_id = r.custid) end)acctid,
(case when r.custid is not null then r.custid else (select i.cif_id from tbaadm.gam i where i.foracid = r.acctid) end)custid
from
(select cast(e.signid as varchar2(12))signid, cast(e.acctid as varchar2(16))acctid, 
cast(f.custid as varchar2(12))custid from svsuser.signotherinfo e, svsuser.signcustinfo f
where e.signid = f.signid)r);

Here my acctid is not unique but it has the same values as in the foracid in the first select Statement. Now I am doing like this:

select * from (select k.foracid,k.acct_name, k.sol_id BRANCH_CODE, s.SOL_DESC BRANCH_NAME,k.ACCT_OPN_DATE,k.schm_code 
    from tbaadm.gam k, tbaadm.sol s
    where acct_cls_flg = 'N' and acct_ownership<>'O'
    and s.sol_id = k.sol_id
    and k.schm_type in('CAA', 'SBA', 'ODA', 'TDA'))m
where m.foracid not in(
select acctid from (select r.signid,
    (case when r.acctid is not null then r.acctid else (select g.foracid from tbaadm.gam g where g.cif_id = r.custid) end)acctid,
    (case when r.custid is not null then r.custid else (select i.cif_id from tbaadm.gam i where i.foracid = r.acctid) end)custid
    from
    (select cast(e.signid as varchar2(12))signid, cast(e.acctid as varchar2(16))acctid, 
    cast(f.custid as varchar2(12))custid from svsuser.signotherinfo e, svsuser.signcustinfo f
    where e.signid = f.signid)r));

I am getting:

ORA-01427: single-row subquery returns more than one row

What am I supposed to do?

Are you sure your query for second resultset is correct? Have you tried running your query for 2nd resultset alone. I think that's where the error is. — A Nice Guy
Well, there is a small possibility, that the execution plan for second query alone and than it's in overall query differs, so that's why second query alone doesn't throw error. — Mikhail

Mikhail Mikhail · Accepted Answer · 2014-05-29T07:30:35

The only places where this error can rise in your query is this two SELECT statement:

select g.foracid from tbaadm.gam g where g.cif_id = r.custid
...
select i.cif_id from tbaadm.gam i where i.foracid = r.acctid

Are you sure, that for each cif_id there is only one row in tbaadm.gam, and so for each foracid? Try checking this with this two queries:

SELECT CIF_ID
FROM tbaadm.gam
HAVING COUNT(1) > 1
GROUP BY CIF_ID;

SELECT FORACID
FROM tbaadm.gam
HAVING COUNT(1) > 1
GROUP BY FORACID;

Also try adding DISTINCT or AGGREGATE functions, if there is more than one row. For example:

select * from (select k.foracid,k.acct_name, k.sol_id BRANCH_CODE, s.SOL_DESC BRANCH_NAME,k.ACCT_OPN_DATE,k.schm_code 
    from tbaadm.gam k, tbaadm.sol s
    where acct_cls_flg = 'N' and acct_ownership<>'O'
    and s.sol_id = k.sol_id
    and k.schm_type in('CAA', 'SBA', 'ODA', 'TDA'))m
where m.foracid not in(
select acctid from (select r.signid,
      (case when r.acctid is not null then r.acctid else (select MIN(g.foracid) from tbaadm.gam g where g.cif_id = r.custid) end)acctid,
    (case when r.custid is not null then r.custid else (select MIN(i.cif_id) from tbaadm.gam i where i.foracid = r.acctid) end)custid
    from
    (select cast(e.signid as varchar2(12))signid, cast(e.acctid as varchar2(16))acctid, 
    cast(f.custid as varchar2(12))custid from svsuser.signotherinfo e, svsuser.signcustinfo f
    where e.signid = f.signid)r));

Hope, this helps.

ORA-01427: single-row subquery returns more than one row select statement

1 Answers