How to Fix Ora-01427 Single-Row Subquery Returns More Than One Row in Select

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

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?

ORA-01427: single-row subquery returns more than one row in oracle

The error message is providing you a HINT for your research "ORA-01427: single-row subquery returns more than one row"

It means that you need to find a query in the SELECT clause returning more than one row. There are other possibilities but in your case, I've identified 2 :

        (SELECT mqidtl.mqtu_inspection_id insp_id,
          p.name,
          CASE
            WHEN (SELECT DISTINCT qlty.decision
              FROM quality_test_values qlty
              WHERE qlty.inspection_id = mqidtl.mqtu_inspection_id
              AND qlty.product_id      = p.id) = 'Tank dry'
            THEN 1
            ELSE 0
          END tank_dry_count,
          
      FROM
        (SELECT mqidtl.mqtu_inspection_id insp_id,
          p.name,
          CASE
            WHEN (SELECT DISTINCT qlty.decision
              FROM quality_test_values qlty
              WHERE qlty.inspection_id = mqidtl.mqtu_inspection_id
              AND qlty.product_id      = p.id) = 'Tank dry'
            THEN 1
            ELSE 0
          END tank_dry_count,

So you need to see why this query is returning more than one row.

SELECT DISTINCT qlty.decision
              FROM quality_test_values qlty
              WHERE qlty.inspection_id = mqidtl.mqtu_inspection_id
              AND qlty.product_id      = p.id) = 'Tank dry'

Use the following query

SELECT qlty.product_id, qlty.inspection_id, count(1), count(distinct qlty.decision)
FROM quality_test_values qlty
GROUP BY qlty.inspection_id , qlty.product_id
HAVING count(distinct qlty.decision) > 1

Hope that this can help you

I don't know your data, so here a testcase


create table quality_test_values
( product_id integer, inspection_id integer, decision varchar2(30))

insert into quality_test_values values (1,1,'Tank dry');
insert into quality_test_values values (1,1,'Other Decision');

SELECT DISTINCT qlty.decision
              FROM quality_test_values qlty
              WHERE qlty.inspection_id = 1
              AND qlty.product_id      = 1;

DECISION       
-------------- 
Tank dry       
Other Decision 

SELECT qlty.product_id, qlty.inspection_id, count(1), count(distinct qlty.decision)
FROM quality_test_values qlty
GROUP BY qlty.inspection_id , qlty.product_id
HAVING count(distinct qlty.decision) > 1

PRODUCT_ID INSPECTION_ID COUNT(1) COUNT(DISTINCTQLTY.DECISION) 
---------- ------------- -------- ---------------------------- 
         1             1        2                            2 

SELECT
          CASE
            WHEN (SELECT DISTINCT qlty.decision
              FROM quality_test_values qlty
              WHERE qlty.inspection_id = W.inspection_id
              AND qlty.product_id      = W.product_id) = 'Tank dry'
            THEN 1
            ELSE 0
          END tank_dry_count
FROM quality_test_values W

ORA-01427: single-row subquery returns more than one row

Proposed solution, ADD a where clause


        CASE
            WHEN (SELECT DISTINCT qlty.decision
              FROM quality_test_values qlty
              WHERE qlty.inspection_id = W.inspection_id
              AND qlty.product_id      = W.product_id
              AND qlty.decision = 'Tank dry') = 'Tank dry'
            THEN 1
            ELSE 0
          END tank_dry_count

Error (single-row subquery returns more than one row)

This is your query:

select en.*,
       (select sf.red, sf.blue, sf.green, sf.yellow from data2 sf )
from data1 en;

A subquery in the select used like this is called a scalar subquery. Such a subquery can be used where a scalar value (i.e. single value such as a number or string) can be used.

Scalar subqueries must meet two conditions:

It returns at most one row.
It returns one column.

The solution in your case is simple, using a LEFT JOIN:

select en.*, sf.red, sf.blue, sf.green, sf.yellow 
from data1 en left join
     data2 sf
     on 1=1;

Or, in Oracle 12C, you can use a lateral join:

select en.*, sf.red, sf.blue, sf.green, sf.yellow 
from data1 en left join lateral
     data2 sf
     on 1=1;

The two look the same in this case, but a lateral join can also be used for a correlated subquery.

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

You have more than one customer in transactions. Presumably, you intend:

WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM TRANSACTIONS)

Which you can also phrase as:

WHERE CUSTOMER_ID = ANY (SELECT CUSTOMER_ID FROM TRANSACTIONS)

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

The problem is this part of your query:

SELECT STG5.OSIGURENI
FROM CIELA_STATUS_STG5 STG5
WHERE CIELA_STATUS.UIC = STG5.UIC

Is returning multiple rows. You need a way to pick exactly one row from the many for the insert.

If it gives an acceptable result, the easiest way is to use MIN() or MAX():

SELECT MAX(STG5.OSIGURENI)
FROM CIELA_STATUS_STG5 STG5
WHERE CIELA_STATUS.UIC = STG5.UIC

So your whole query would then be:

UPDATE CIELA_STATUS 
SET CIELA_STATUS.OSIGURENI = (
  SELECT MAX(STG5.OSIGURENI)
  FROM CIELA_STATUS_STG5 STG5
  WHERE CIELA_STATUS.UIC = STG5.UIC)
WHERE EXISTS (
  SELECT STG5.OSIGURENI
  FROM CIELA_STATUS_STG5 STG5
  WHERE CIELA_STATUS.UIC = STG5.UIC
  AND nvL(CIELA_STATUS.OSIGURENI, 9999) <> STG5.OSIGURENI)

If MAX() doesn’t give the proper result, you’ll have to engineer a query that selects the appropriate row from the many that match.

single-row subquery returns more than one row - when query should return 1 row

The error seems pretty obvious. This subquery:

(select id from case where ssn = DepntSsn)

returns more than one row. Well, the reason might have to do with the fact that this is the same table, so this is really doing:

(select c2.id from case c2 where c2.ssn = c2.DepntSsn)

which is certainly not what you intend. I suspect you want something like this:

select . . .,
       (select c2.id from case c2 where c2.ssn = c.DepntSsn)
from case c
. . .

When your query has more than one table reference, you should always qualify all column references. This is particularly important for correlated subqueries!

Also, case is a lousy name for a table, because it is a SQL keyword.

ORA-01427 Single row subquery returns more than 1 row

From the CASE expression documentation:

For both simple and searched CASE expressions, all of the return_exprs must either have the same datatype (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric datatype. If all return expressions have a numeric datatype, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

The return_expr of a CASE statement expects a single value so your sub-query:

( select value1 from test1 where department=t3.DEPARTMENT)

is what is raising the exception.

Instead use the filter on the sub-query:

select distinct t3.*
from   test3 t3
       INNER JOIN test1 t1
       ON ( t3.department=t1.department )
WHERE  t3.value1 not in (
         select value1
         from   test1
         where  department=t3.DEPARTMENT
         AND    t1.CHECK_CONDITION1 = 1
       )

Which, for your test data, outputs:


DEPARTMENT | VALUE1 | VALUE2 | VALUE3  
---------: | :----- | :----- | :-------
         1 | Z      | Y      | Whatever

db<>fiddle here

How to Fix Ora-01427 Single-Row Subquery Returns More Than One Row in Select