How to fix Ora-01427 single-row subquery returns more than one row in select

Question

When i execute the following query  i get the message like       Ora-01427 single-row subquery returns more than one row    SELECT E I EmpID AS EMPID         E I EMPCODE AS EMPCODE         E I EmpName AS EMPNAME         REPLACE TO CHAR A I REQDATE   DD-Mon-YYYY             AS FROMDATE         REPLACE TO CHAR A I ENDDATE   DD-Mon-YYYY             AS TODATE         TO CHAR NOD  AS NOD         DECODE A I DURATION                 FD                  FullDay                  FN                  ForeNoon                  AN                  AfterNoon   AS DURATION         L I LeaveType AS LEAVETYPE         REPLACE TO CHAR  SELECT C I WORKDATE                          FROM T COMPENSATION C                         WHERE C I COMPENSATEDDATE   A I REQDATE                           AND C I EMPID   A I EMPID                           DD-Mon-YYYY                                           AS WORKDATE         A I REASON AS REASON         AP I REJECTREASON AS REJECTREASON   FROM T LEAVEAPPLY A  INNER JOIN T EMPLOYEE MS E     ON A I EMPID   E I EmpID    AND UPPER E I IsActive     YES     AND A I STATUS    1   INNER JOIN T LeaveType MS L     ON A I LEAVETYPEID   L I LEAVETYPEID   LEFT OUTER JOIN T APPROVAL AP     ON A I REQDATE   AP I REQDATE    AND A I EMPID   AP I EMPID    AND AP I APPROVALSTATUS    1   WHERE E I EMPID  lt  gt   22   ORDER BY A I REQDATE DESC   when i execute this without ORDER BY A I REQDATE DESC it returns 100 rows

User · Answer

The only subquery appears to be this - try adding a ROWNUM limit to the where to be sure:

(SELECT C.I_WORKDATE
         FROM T_COMPENSATION C
         WHERE C.I_COMPENSATEDDATE = A.I_REQDATE AND ROWNUM <= 1
         AND C.I_EMPID = A.I_EMPID)

You do need to investigate why this isn't unique, however - e.g. the employee might have had more than one C.I_COMPENSATEDDATE on the matched date.

For performance reasons, you should also see if the lookup subquery can be rearranged into an inner / left join, i.e.

 SELECT 
    ...
    REPLACE(TO_CHAR(C.I_WORKDATE, 'DD-Mon-YYYY'),
            ' ',
            '') AS WORKDATE,
    ...
 INNER JOIN T_EMPLOYEE_MS E
    ...
     LEFT OUTER JOIN T_COMPENSATION C
          ON C.I_COMPENSATEDDATE = A.I_REQDATE
          AND C.I_EMPID = A.I_EMPID
    ...

User · Answer

SELECT C I WORKDATE          FROM T COMPENSATION C          WHERE C I COMPENSATEDDATE   A I REQDATE AND ROWNUM  lt   1          AND C I EMPID   A I EMPID

User · Answer

Use the following query   SELECT E I EmpID AS EMPID         E I EMPCODE AS EMPCODE         E I EmpName AS EMPNAME         REPLACE TO CHAR A I REQDATE   DD-Mon-YYYY             AS FROMDATE         REPLACE TO CHAR A I ENDDATE   DD-Mon-YYYY             AS TODATE         TO CHAR NOD  AS NOD         DECODE A I DURATION                 FD                  FullDay                  FN                  ForeNoon                  AN                  AfterNoon   AS DURATION         L I LeaveType AS LEAVETYPE         REPLACE TO CHAR  SELECT max C I WORKDATE                           FROM T COMPENSATION C                         WHERE C I COMPENSATEDDATE   A I REQDATE                           AND C I EMPID   A I EMPID                           DD-Mon-YYYY                                           AS WORKDATE         A I REASON AS REASON         AP I REJECTREASON AS REJECTREASON   FROM T LEAVEAPPLY A  INNER JOIN T EMPLOYEE MS E     ON A I EMPID   E I EmpID    AND UPPER E I IsActive     YES     AND A I STATUS    1   INNER JOIN T LeaveType MS L     ON A I LEAVETYPEID   L I LEAVETYPEID   LEFT OUTER JOIN T APPROVAL AP     ON A I REQDATE   AP I REQDATE    AND A I EMPID   AP I EMPID    AND AP I APPROVALSTATUS    1   WHERE E I EMPID  lt  gt   22   ORDER BY A I REQDATE DESC   The trick is to force the inner query return only one record by adding an aggregate function  I have used max   here   This will work perfectly as far as the query is concerned  but  honestly  OP should investigate why the inner query is returning multiple records by examining the data  Are these multiple records really relevant business wise

[sql] How to fix Ora-01427 single-row subquery returns more than one row in select?

Examples related to sql

Examples related to oracle