ORA-01427:单行子查询返回多个行 [英] ORA-01427: single-row subquery returns more than one row

问题描述

我的查询如下:

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')

我遇到异常 ORA-01427:单行子查询返回多行，如果我的行中有多行具有相同的clasisymbolicname，尽管它们的net headid值不同，并且在这种情况下不应该发生此异常，请对此提供建议.

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.

推荐答案

问题是START WITH期望单个值，而我的子查询返回了多个值，所以我更改了查询 from :

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')

至:

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') 

它工作正常.

这篇关于ORA-01427:单行子查询返回多个行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！