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

问题描述

我的查询如下:

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:单行子查询返回多个行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！