当我执行以下查询时,我得到的消息就像
“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,'') 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),'') 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
当我执行此操作而没有ORDER BY A.I_REQDATE DESC时,它返回100行…
解决方法
使用以下查询：
SELECT E.I_EmpID AS EMPID,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),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
诀窍是通过添加聚合函数强制内部查询只返回一条记录(我在这里使用了max()).就查询而言,这将完美地工作,但是,老实说,OP应该通过检查数据来调查内部查询返回多个记录的原因.这些多条记录真的与商业明智相关吗？
