Question

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

SELECT REPORT_ID,
REPORT_NAME,
B.COMPANYID,
B.ORGNAME,
(SELECT INSTANCE_ID
FROM M_REPORT_INSTANCE C
WHERE A.REPORT_ID = C.REPORT_ID
AND B.COMPANYID = C.COMPANYID
AND C.REPORT_YEAR = '2011'
and REPORT_QUARTER = '04') AS INSTANCE_ID
FROM M_AUDIT_REPORT A, EOSORG_T_ORGANIZATION B
WHERE a.REPORT_ID IN ('JT_Y_002_00_001')
AND (B.PARENTORGID = 71 OR B.ORGID = 71 or B.ORGCODE LIKE '45%')
AND B.ORGTYPEID NOT IN (91)
and B.COMPANYID <> '00'
and orgTypeID not in (2, 7)
and B.ORGCODE LIKE '45%'
order by a.report_id, b.orgcode;
这个应该怎么改呢 急~~~

Answer 1

SELECT REPORT_ID,

       REPORT_NAME,

       B.COMPANYID,

       B.ORGNAME,

       (SELECT C.INSTANCE_ID

          FROM M_REPORT_INSTANCE C,

          M_AUDIT_REPORT A       

         WHERE A.REPORT_ID = C.REPORT_ID

           AND B.COMPANYID = C.COMPANYID

           AND C.REPORT_YEAR = '2011'

           AND REPORT_QUARTER = '04') AS INSTANCE_ID

  FROM M_AUDIT_REPORT        A,

       EOSORG_T_ORGANIZATION B

 WHERE A.REPORT_ID IN ('JT_Y_002_00_001')

   AND B.PARENTORGID = 71 

   OR B.ORGID = 71 

   OR B.ORGCODE LIKE '45%'

   AND B.ORGTYPEID NOT IN (91)

   AND B.COMPANYID <> '00'

   AND ORGTYPEID NOT IN (2, 7)

   AND B.ORGCODE LIKE '45%'

 ORDER BY A.REPORT_ID,

          B.ORGCODE;

追问

还是那个错误

追答

排查一下限制条件就可以了吧.