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

Question

my query is as follows:

SELECT DISTINCT * 
FROM (SELECT depts."ID", depts.arabic_description 
      FROM sng_v_org_unit_departments depts 
      INNER JOIN (SELECT "ID", arabic_description, ouh_id   
                  FROM sng_v_org_unit_headers 
                  START WITH "ID" = 
                  (SELECT headid 
                   FROM emppirmesion per 
                   inner join  empldabdetail empinfo 
                           on per.emprecid = empinfo.recid 
                   where lower(empinfo.shortname) =  lower('ibmadmin') and 
                         per.headid > 0 and 
                         per.clasisymbolicname = 'SoHiring') 
                  CONNECT BY PRIOR "ID" = ouh_id) heads 
      ON depts.ouh_id = heads."ID"
      UNION 
      SELECT per.depid, depts.arabic_description 
      FROM emppirmesion per 
      inner join empldabdetail empinfo on per.emprecid = empinfo.recid 
      inner join sng_v_org_unit_departments depts on per.depid = depts."ID" 
      where lower(empinfo.shortname) = lower('ibmadmin') and 
            per.depid > 0 and 
            per.clasisymbolicname = 'SoHiring')

I am getting an exception ORA-01427: single-row subquery returns more than one row if i have more than one row in the emppirmesion that have the same clasisymbolicname although they have differnet headid values and this exception shouldn't occur in this case, please advise about that.

If you do have more than one row in emppirmesion with the same classisymbolicname, would you get different headid values? What should happen with your query in this case, should it just START WITH a random one, or the first one? — beny23
@beny23, i do have more than one row in emppirmesion with the same classisymbolicname and with different headid values, but when executing the query i only get the exception. — Mahmoud Saleh
Yes, my point is you would get the exception because Oracle is expecting START WITH = single value but your query is providing it with START WITH = multiple values, so you would have to redesign your query, but without knowing what you are trying to achieve, that's a bit difficult... — beny23

Mahmoud Saleh Mahmoud Saleh · Accepted Answer · 2013-07-08T09:04:49

Issue was the START WITH expects single value and my subquery was returning multiple values, so i changed the query from:

START WITH "ID" = 
                  (SELECT headid 
                   FROM emppirmesion per 
                   inner join  empldabdetail empinfo 
                           on per.emprecid = empinfo.recid 
                   where lower(empinfo.shortname) =  lower('ibmadmin') and 
                         per.headid > 0 and 
                         per.clasisymbolicname = 'SoHiring')

to:

Where "ID" in 
                  (SELECT headid 
                   FROM emppirmesion per 
                   inner join  empldabdetail empinfo 
                           on per.emprecid = empinfo.recid 
                   where lower(empinfo.shortname) =  lower('ibmadmin') and 
                         per.headid > 0 and 
                     per.clasisymbolicname = 'SoHiring')

and it works fine.

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

1 Answers