Ora 01427 single row subquery returns more than one row ошибка

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?

Hello, guys and friends

I run this SQL on my Oracle Database Server 11.2.0.4.0 on SQL*Plus utility,

SET ECHO      ONSET HEADING   ONSET FEEDBACK  ONSET VERIFY    ONSET LINESIZE 250SET PAGESIZE 250SELECT      (SELECT host_name FROM v$instance)      || ', ' ||      (SELECT value FROM v$parameter WHERE name = 'db_unique_name')      || ', ' ||      (SELECT              start_time || ', ' ||              end_time || ', ' ||              output_device_type || ', ' ||              status || ', ' ||              input_type || ', ' ||              ltrim(input_bytes_display) || ', ' ||              ltrim(output_bytes_display) || ', ' ||              ltrim(input_bytes_per_sec_display) || ', ' ||              ltrim(output_bytes_per_sec_display) || ', ' ||              time_taken_display       FROM  v$rman_backup_job_details       WHERE output_device_type = 'DISK'       -- AND To_Char(start_time,'dd-mm-yy') = To_Char(sysdate - 1,'dd-mm-yy')      )FROM dual/

ERROR at line 6:ORA-01427: single-row subquery returns more than one row

This error ORA-01247 indicates that the 3rd SELECT statement for v$rman_backup_job_details should return the value of more than one row.

If I use PL/SQL how to implement this function/requirement? Thanks in advance.

Best Regards

Quanwen Zhao

Have you thought about using a JOIN instead of the subquery:

select 
  t.test_case_id,
  t.status,
  case when t.status = 'FAIL' then l.defect_id  
    end as defect1_id
from test t
left join link1 l
  on t.test_case_id = l.test_case_id

See SQL Fiddle with Demo

This will return both records, then you can decide which item to return in your final result.

Result:

| TEST_CASE_ID | STATUS | DEFECT1_ID |
--------------------------------------
|           20 |   FAIL |        500 |
|           30 |   FAIL |        500 |
|           30 |   FAIL |        400 |
|           10 |   PASS |     (null) |

Based on your comment, if you are using Oracle 11g, then you can use the LISTAGG() function to combine the records into one row:

select 
  t.test_case_id,
  t.status,
  case 
    when t.status = 'FAIL' 
    then listagg(l.defect_id, ', ')
          within group (order by l.defect_id)
  end as defect1_id
from test t
left join link1 l
  on t.test_case_id = l.test_case_id
group by t.test_case_id, t.status

See SQL Fiddle with Demo

Result:

| TEST_CASE_ID | STATUS | DEFECT1_ID |
--------------------------------------
|           10 |   PASS |     (null) |
|           20 |   FAIL |        500 |
|           30 |   FAIL |   400, 500 |

Have you thought about using a JOIN instead of the subquery:

select 
  t.test_case_id,
  t.status,
  case when t.status = 'FAIL' then l.defect_id  
    end as defect1_id
from test t
left join link1 l
  on t.test_case_id = l.test_case_id

See SQL Fiddle with Demo

This will return both records, then you can decide which item to return in your final result.

Result:

| TEST_CASE_ID | STATUS | DEFECT1_ID |
--------------------------------------
|           20 |   FAIL |        500 |
|           30 |   FAIL |        500 |
|           30 |   FAIL |        400 |
|           10 |   PASS |     (null) |

Based on your comment, if you are using Oracle 11g, then you can use the LISTAGG() function to combine the records into one row:

select 
  t.test_case_id,
  t.status,
  case 
    when t.status = 'FAIL' 
    then listagg(l.defect_id, ', ')
          within group (order by l.defect_id)
  end as defect1_id
from test t
left join link1 l
  on t.test_case_id = l.test_case_id
group by t.test_case_id, t.status

See SQL Fiddle with Demo

Result:

| TEST_CASE_ID | STATUS | DEFECT1_ID |
--------------------------------------
|           10 |   PASS |     (null) |
|           20 |   FAIL |        500 |
|           30 |   FAIL |   400, 500 |