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

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 <> '22' ORDER BY A.I_REQDATE DESC 

when i execute this without ORDER BY A.I_REQDATE DESC it returns 100 rows...

3 Answers

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

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 ... 

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