ORA-01427: single-row subquery returns more than one row - I get this error even though I use SELECT DISTINCT

ora-01427 single-row subquery returns more than one row update
single-row subquery returns more than one row case statement
how to debug single-row subquery returns more than one row
single-row subquery returns more than one row redshift
subquery returns more than 1 row
single-row subquery returns more than one row how to find the duplicate
single-row subquery returns more than one row stack overflow
cause: cleanup_node failed due to ora-01427: single-row subquery returns more than one row

I am trying to make a query that selects all the drivers that were fined in the same place where another driver was fined.

SELECT DISTINCT(c.IdSofer), s.NumeSofer
FROM Soferi s
INNER JOIN contraventii c ON c.IdSofer= s.IdSofer
HAVING (SELECT DISTINCT IdLocContr FROM Contraventii c 
INNER JOIN Soferi s ON c.IdSofer=s.IdSofer
INNER JOIN Localitati l ON s.IdLocSofer=l.IdLoc
WHERE s.NumeSofer='Maneta Gheorghe' AND l.DenLoc='Pocreaca' AND l.Jud='IS' GROUP BY IdLocContr) = c.IdLocContr
GROUP BY s.NumeSofer, c.IdLocContr, c.IdSofer;

This is what I tried but I get the error message

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

If I run only only the SELECT statement between parentheses it works fine. It shows the id of cities (two cities) where this driver was fined. But if I try to run the code as I wrote here I get this error.

EDIT

This is the SQL from my comment...

SELECT DISTINCT(c.IdSofer)
      ,s.NumeSofer
  FROM Soferi s INNER JOIN contraventii c
    ON c.IdSofer= s.IdSofer
 GROUP BY s.NumeSofer
         ,c.IdLocContr
         ,c.IdSofer
HAVING (SELECT DISTINCT IdLocContr
          FROM Contraventii c INNER JOIN Soferi s
            ON c.IdSofer=s.IdSofer INNER JOIN Localitati l
                                           ON s.IdLocSofer=l.IdLoc
         WHERE s.NumeSofer = 'Maneta Gheorghe'
           AND l.DenLoc = 'Pocreaca'
           AND l.Jud = 'IS'
      GROUP BY IdLocContr) = c.IdLocContr
WHERE c.IdLocContr IN (
    SELECT DISTINCT IdLocContr
    FROM Contraventii c 
        INNER JOIN Soferi s ON c.IdSofer=s.IdSofer
        INNER JOIN Localitati l ON s.IdLocSofer=l.IdLoc
    WHERE s.NumeSofer = 'Maneta Gheorghe' AND l.DenLoc = 'Pocreaca' AND l.Jud = 'IS'
)

Just a few notes for your benefit.

The distinct isn't really necessary inside an in subquery although I left it there. Also you were using both distinct and group by which were redundant for that query. Having comes into play when you want to filter based on group aggregates.

ORA-01427: single-row subquery returns more than one row, WHERE c.IdLocContr IN ( SELECT DISTINCT IdLocContr FROM Contraventii c INNER JOIN Soferi s ON c.IdSofer=s.IdSofer INNER JOIN  The ORA-01427 is a straightforward query error in Oracle. The cause of the error is a subquery returning more than one row of information. This error in multi-row returns on the subquery originates from an outer query failing to use appropriate, designated keywords to specify values for comparison in the subquery.

It's too long to put in comment, so I put it here. Debugging is part of programmer's job. Have you checked whether the query below returns one row or not? If not, then that's what you have to fix.

SELECT DISTINCT IdLocContr 
FROM Contraventii c 
INNER JOIN Soferi s ON c.IdSofer=s.IdSofer
INNER JOIN Localitati l ON s.IdLocSofer=l.IdLoc
WHERE s.NumeSofer='Maneta Gheorghe' AND l.DenLoc='Pocreaca' AND l.Jud='IS' 
GROUP BY IdLocContr

From the look at it, I doubt it will only return 1 row.

How to Resolve ORA-01427: single-row subquery returns more than , There're two error patterns related to ORA-01427 described in this post: If SQL engine expects your subquery to return a single row, it may throw Then what operator is used to prevent ORA-01427 in SELECT statement? as one, you have to limit the number of rows returned of queries to only one row. it just a result of where clause, your where clause must be returning more than one row.Have you used some primary key?? Its always better to use primary key in where clause because it ensure that only required rows would be output.

your having clause should return one value:

SELECT DISTINCT IdLocContr
          FROM Contraventii c INNER JOIN Soferi s
            ON c.IdSofer=s.IdSofer INNER JOIN Localitati l
                                           ON s.IdLocSofer=l.IdLoc
         WHERE s.NumeSofer = 'Maneta Gheorghe'
           AND l.DenLoc = 'Pocreaca'
           AND l.Jud = 'IS'
           AND rownum<=1
      GROUP BY IdLocContr

Distinct SUB-QUERY causes ORA-01427: single-row subquery , I have two subqueries within a select statement - the top quesry is running i get the ORA-01427: single-row subquery returns more than one row despite the generally that error is caused by using '=' instead of 'IN' when  So I'm trying to distinct values before I use listagg. The subquery does return more then one record and I think this how I want it. I believe it suppose to work, but I get ORA-01427: single-row subquery returns more than one row Any help would be appreciated.

ORA-01427: Single-Row Subquery Returns More Than One Row, ORA-01427: Single-Row Subquery Returns More Than One Row In order to overcome this error, you have to make the result single. in sn_voucher_d with one unique value from that sub query or update one single row in that table with one A query that you use on the right side of a SET clause in an update statement 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

Solved: ORA-01427: single-row subquery returns more than o , I searched the forum but could not get any satisfying result. ORA-01427: single-​row subquery returns more than one row The query returns multiple rows and even though i used where..in or where exists clause i am not able to select distinct ifp.netprice I would suggest to rewrite the query you may find the problem. ORA-01427 single-row subquery returns more than one row Cause: The outer query must use one of the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row. Action: Use ANY, ALL, IN, or NOT IN to specify which values to compare or reword the query so only one row is retrieved.

Ask TOM "listagg gives ORA-01427: single-row subquery retur", The subquery does return more then one record and I think this how I I believe it suppose to work, but I get ORA-01427: single-row subquery returns more than one row SQL> select 2 d.deptno, 3 listagg( 4 ( select distinct ename from "​Oracle performs a correlated subquery when a nested subquery  I tried to execute this in Oracle:update sn_voucher_d set payer_n = (select a.account_link_code_n from stg_lgsm_service_mast a, sn_voucher_d c where a.mobl_num_voice_v= c.subscriber_id)I receive the following error:ORA-01427: single-row subquery returns more than one rowCan anyone help?

Comments
  • share sample data and expected output
  • In a SQL query, the HAVING clause must follow a GROUP BY. In the SQL you posted, HAVING appears before GROUP BY.
  • I have put a photo with the extras from DB where are shown only the fines from the cities where the driver was finned. The output has to be the driver name "s.NumeSofer"
  • That should be a correlated subquery in the where clause I suspect. Definitely not a typical having.
  • Probably a good idea to learn SQL syntax before writing complicated query.
  • If this solved your problem you have a responsibility to accept the answer by clicking on the little check mark under the upvote/downvote totals for the answer. This will turn it green, indicating that the answer has been accepted, and will award the poster 15 points. Thanks.
  • Thank you for informing me. I didn't knew that. I marked it as accepted. Thank you again!
  • It returns two rows. One for every city where the driver was finned.
  • @BeginerUser Then there's your problem.
  • But it has to. Because the driver was finned in two different cities, with two different id. As I said, this SELECT statement worked just fine. The problem was (as shawnt00 pointed in his solution) that I was using HAVING clause instead WHERE value IN. I have also tried with WHERE, but I was using = instead of IN and that was the problem.
  • But, the put equality in the query, so there can't be more than 1 value. You'll need to change your query if you want to support multiple values.